topic Re: Measure calculated for rolling specified period in DAX Commands and Tips

Measure calculated for rolling specified period

ewakol — Tue, 24 Sep 2024 08:25:58 GMT

Hello,

i calculate Demand Plan Monthly Error as a measure DPME = |Demand Plan - Actual Sales| / Actual Sales

The result looks similar like that:

i also give informaction how DPME look for last 12 month altogether. In showed example is 77% (ist not average, DPME is calculated for last 12 mc altogether). I have to prepare a chart as above but instead of showing for yyyy-mm data for a given month, I have to show, data for the last 12 months. Example:

For data prepared in Sep-24

in 2024.8 need to show DPME for period from 2024.8 to 2023.9 (for 12 months, but calculated from the month that was 1 months ago)
in 2024.7 need to show DPME for period from 2024.7 to 2023.8 (for 12 months, but calculated from the month that was 2 months ago)
in 2024.6 need to show DPME for period from 2024.6 to 2023.7 (for 12 months, but calculated from the month that was 3 months ago)
(......)

in 2023.9 need to show DPME for period from 2024.9 to 2022.10 (for 12 months, but calculated from the month that was 12 months ago)

is it posiible to do some measures like that? which can be put on chart on the top?

Re: Measure calculated for rolling specified period

bhanu_gautam — Tue, 24 Sep 2024 11:06:40 GMT

ewakol ,

Create a Date Table: Ensure you have a Date table in your model. If not, create one. This table should have a continuous range of dates covering the period of your data.

Create the DPME Measure: Use DAX to create a measure that calculates the DPME for the last 12 months.

DPME 12 Month Rolling =
VAR CurrentDate = MAX('Date'[Date])
VAR StartDate = EDATE(CurrentDate, -12) + 1
RETURN
CALCULATE(
SUMX(
'Sales',
ABS('Sales'[Demand Plan] - 'Sales'[Actual Sales]) / 'Sales'[Actual Sales]

Create a Line Chart: Add a line chart to your Power BI report.

Add Data to the Chart:

Drag the Date field from your Date table to the Axis of the chart.
Drag the DPME 12 Month Rolling measure to the Values of the chart.
Format the Chart: Adjust the chart formatting as needed to display the data clearly.

Re: Measure calculated for rolling specified period

ewakol — Thu, 26 Sep 2024 09:24:30 GMT

Than you very much for reply, i tried this solution but it doesny work please see below:

Re: Measure calculated for rolling specified period

Anonymous — Tue, 08 Oct 2024 08:32:46 GMT

Hi ewakol ,

Have a good day. Did you resolve the issue? If the issue persists, could you please share sample data and expected output?

Best Regards,

Wearsky