How to Get Previous Month Value in Dax Mesure for first Month in Filter Context

[mjanecek]

I am using the below measure to calculte the variance between premium current month to previous month.

Premium Variance =

var pmprem = CALCULATE([Premium],PREVIOUSMONTH('Calendar'[Date]))

var cmprem = [Premium]

RETURN cmprem - pmprem

When my report is filtered on all months of the current year to date for example, the filter context does not see the value of premium for Dec of  2021 and my RETURN value has no previous month premium to subtract from current  month premium.

This results in the below waterfall visual having the value for the current month's premium for 1/1/22(the first month in the current filter context),instead of the difference/variance between current and previous month premium because there is nothing the measure can see for the previous month.

Is there a way to change the DAX to get the previous month for the first month in the filter context , or do I need to add a column for the data in my model?

[igrandey89 · Accepted solution]

Have you tried using:

DATEADD: https://dax.guide/dateadd/ 
For example

Sales Previous Month = 
CALCULATE(
    [Sales],
    DATEADD(DimDate[FullDateAlternateKey],-1,MONTH)
)

PARALLELPERIOD might help, too: https://dax.guide/parallelperiod/

[mjanecek]

Hi: I am trying to write a DAX expression that returns the value of the Avg Prem measure for a month that is NOT in the current fitler context.

Our organization has a table called Standard Date that returns a max and min value for a date range selected by the user in the filter pane.

Dax calculations then use the Min and Max values of this table to limit the calculations of measures to the dates selected.

What I would like to be able to do is calculate the value of a measure within my dax statement for the month BEFORE the first one that is currently selected.  

I tried the below code.

Avg Premium Variance =

--Captures Minimum accounting month selected in the visualby comparing it to the Min Date selected in Standard Dates so each subsequent month can be compared to it

var minactmnth =  

                        CALCULATE(

                                min('Calendar'[Accounting Month]),

                                   'Calendar'[Accounting Month] = VALUES('Standard Dates'[MinDate])

                                )

var pmprem = if(

            --If currently selected month = min. selected month calculate previous month premium 1 month behind current filter context.

            max('Calendar'[Accounting Month]) = minactmnth,

                CALCULATE([Avg Prem Measure],

                            'Calendar'[Accounting Month]>EOMONTH(VALUES('Standard Dates'[MinDate]),-2),

                            'Calendar'[Accounting Month]<=EOMONTH(VALUES('Standard Dates'[MinDate]),-1)

                        )

                ,

                --Otherwise grab previous month of avg premium

                CALCULATE([Avg Prem Measure],

                PREVIOUSMONTH('Calendar'[Date])

                        )

                )

--Set Current Month Premium to exisiting avg premium measure

var cmprem = [Avg Prem Measure]

RETURN

"variance: " & cmprem-pmprem & " currentprem: " & cmprem & " prevMonthPrem: " & pmprem & " MinAcctMonth: " & minactmnth & " SelectedActMonth: " & max('Calendar'[Accounting Month])& " MinStdDt-2: " & EOMONTH(VALUES('Standard Dates'[MinDate]),-2) & " MaxStdDt-1: " & EOMONTH(VALUES('Standard Dates'[MinDate]),-1)  

I am getting no previous month premium for January, event though the output of the measure confirms the first part of my if statement did run for the month of Jan 2022 because the Selected accounting Month did = the MinAcctMonth as shown below.

I thought the calculate function allowed you to override the filter context that is selected by a visual, but I guess not in this case.  Is there a way to calculate the value of [Avg Prem] within this Dax statement for Dec of 2021?

[Mahesh0016]

_Total ListPrice = CALCULATE(SUM(TrainingSample2[ListPrice]),ALL(DimDate[Date]))

*******************************

_Premium Variance =

var pmprem = CALCULATE([Total ListPrice],PREVIOUSMONTH(DimDate[Date]))

var cmprem = [_Total ListPrice]

RETURN cmprem - pmprem

@mjanecek Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!