Search code examples
powerbidaxpowerquerypowerbi-desktop

PowerBI & DAX: How could I have a measure be plotted on a graph following a custom rule?

I have a table with these columns:

Event Table
Event ID
Event Month
Event Year
Event Type
Event Total Hours

I have this measure referring to this table :

Frequency Rate = IF(AVERAGE(Event[Event Total Hours])<>BLANK(),
 10^6*(CALCULATE(COUNT(Event[Event ID]),
FILTER(Event,Event[Event Type]="Type A"))
/AVERAGE(Event[Event Total Hours]),BLANK())

When I plot this on a graph using Event Month and Event Year as the X axis, I naturally get something like this:

Event Measure Plotted

Up to here, everything is working as expected.

For exemple, in the graph for 2022-09, I get a value of about 6. This corresponds to Events that happened that month and that month only.

What I need help with is that I need this measure to always span over its last 12 months. I need for the value for 2022-09 to apply the measure for all events that happened between 2021-09 up to 2022-09.

Unfortunately, I have no idea how to do such a thing. Any help is greatly appreciated ! Thanks in advance

Solution

  • Nest your measure inside a CALCULATE(...,DATESINPERIOD()) function.

    CALCULATE(
    /*existing expression*/,
    DATESINPERIOD( 'Calendar'[Date], LASTDATE( 'Calendar'[Date] ), -12, MONTH )
)

    This requires a related Calendar table to work. It evaluates the last date in current context, and evaluates the entire expression over the preceding 12 months.