Multi-calendar previous month forecast measure calculation issue

[raduchirila]

I have a report that calculates a variance between the current month actuals and the previous month forecasts. The bars are highlighted in red:

The Reporting Month filter is linked to the dim_ReportingCalendar table, while the time axis of the visuals are linked to dim_Calendar table:

The problem I have is to calculate the forecast of the previous selected month.

Let’s take an example:

In August the [Actuals] is 73. In July the [Forecast] for August is 143 => the variance is -70

In July the [Actuals] is 88. In June the [Forecast] for July is 87 => the variance is 1

Measures defined:

Forecast_USD = CALCULATE(SUM(MonthlyCost[Value]), FILTER(MonthlyCost, AND(CONTAINSSTRING(MonthlyCost[Attribute],"ForecastFYCurr??"),LEN(MonthlyCost[Attribute])=16)))

This measure calculates correctly the forecast using the data available for the Reporting Month selected. E.g. for July we will have forecasts from August until the end of the year

Forecast_PREV_USD = CALCULATE([Forecast_USD], PREVIOUSMONTH(dim_ReportingCalendar[Date]))

This measure calculates correctly only one month back from the selected Reporting Month and is blank for all previous months.

Basically, we would need a smarter way to calculate PREVIOUSMONTH(PREVIOUSMONTH(… [N times] going back from the Reporting Month selected, for the current year.

Sample data:

July data (forecast for Aug-Dec)                 

August data (forecast for Sept-Dec) 

RecordDate is linked to the dim_Calendar table, ReportingDate is linked to the dim_ReportingCalendar

Already tried a few things:

Forecast_PREV_USD = CALCULATE([Forecast_USD], FILTER(ALL(MonthlyCost), DATEADD(MonthlyCost[RecordDate], -1, MONTH) = (MonthlyCost[ReportingDate])))

Returns the same value for all months

Forecast_PREV_USD = CALCULATE([Forecast_USD], FILTER(MonthlyCost, DATEADD(MonthlyCost[RecordDate], -1, MONTH) = (MonthlyCost[ReportingDate])))

Returns only one month depending on the Reporting Month filter

Any suggestion would be much appreciated.

[raduchirila]

Hi @Tanushree_Kapse 

I tried the measure, but it always return blank. I added a REMOVEFILTERS() or REMOVEFILTERS(dim_ReportingCalendar):

Forecast_PREV_USD = CALCULATE([Forecast_USD], REMOVEFILTERS(), PARALLELPERIOD(MonthlyCost[RecordDate], -1, MONTH))

...butwithout success.

I'm curretly looking with DAX Studio to understand better what filters are applied.

Thanks for your help.

[Tanushree_Kapse]

Hi @raduchirila ,

YOu can use PARALLELPERIOD function for this:

Use the below measure:

Forecast_PREV_USD = CALCULATE([Forecast_USD], PARALLELPERIOD(MonthlyCost[RecordDate], -1, MONTH))

Mark this as a solution, if I answered your question. Kudos are always appreciated.

Thanks.