Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
I tried the measure, but it always return blank. I added a REMOVEFILTERS() or REMOVEFILTERS(dim_ReportingCalendar):
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |