Forum Discussion

ewakol's avatar
ewakol
Icon for Helper II rankHelper II
1 year ago

Measure calculated for rolling specified period

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?

measure
period
rolling

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    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.

    • ewakol's avatar
      ewakol
      Icon for Helper II rankHelper II
      1 year ago

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

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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