CODEWITHSUNDEEP
excelpowerbidaxpowerbi-desktop
MmVv
MmVv

Reputation: 553

Before previous month measure in (DAX) Power BI

I am struggling with DAX measure to get 2nd month from today, or in business terms, before previous month (so lets say May from now).

I am using this DAX:

    Prev-2Months = 
CALCULATE (
    CALCULATE (
        [Comp_Spend],
        MONTH ( CCC[Date] )
            = IF (
                MONTH ( MAX ( CCC[Date] ) ) <= 2,
                10 + MONTH ( MAX ( CCC[Date] ) ),    // similar DAX is for Month 
                                                        before (so June) with a 
                                                        little tweak
                MONTH ( MAX ( CCC[Date] ) ) - 2
            ),
        YEAR ( CCC[Date] )
            = IF (
                MONTH ( MAX ( CCC[Date] ) ) <= 2,
                YEAR ( MAX ( CCC[Date] ) - 1 ),
                YEAR ( MAX ( CCC[Date] ) )
            ),
        ALL ( V_Dim_Dates ),
        KEEPFILTERS ( CCC[ClinicID] )
    )
)

When it comes to February 2022 with slicer I am getting Blank values, assuming that Fiscal Year ends on 09/30. How can I solve this to not getting blanks for this "year transition" case?

Upvotes: 0

Views: 2295

Answers (1)

MmVv
MmVv

Reputation: 553

Hmmm I guess I solved it using DATEADD, where my huge formula gives the same result.. :/

Prev-1M = CALCULATE ([Compliance_Spend],DATEADD(Commercial[Date],-1,MONTH),REMOVEFILTERS(V_Dim_Dates_Extended))

So I can easily adjust if I wanna 1 or 2 or 3 months to have as previous..

It is good to know that there are XYZ options in DAX how you can obtain the same thing.

Credits for suggesting: @Anonymous

Upvotes: 0

Related Questions