Chart to show 3 months if data by selected date

[Craig23]

Hi,

I want to create a line chart that shows the number of enquiries by 3 month periods based on a selected month. Ie, If May 2023 is selected the last data point will be the sum of enquires for March - May 2023, the previous data point would be for December - February etc. I have a measure to sum the 3 month periods but when I produce the chart it shows every month (so its showing data as a rolling 3 months). I only want to show the highlighted data points. I assume a need a dynamic date table for the x-axis that pulls in the selected month and then calculates the other months to show but I can't get this to work?

. 

I know this would be easy to do by financial quarters but the client wants to choose the 3 month period as ending on any month.

Thanks.

[v-rzhou-msft · Accepted solution]

Hi @Craig23 ,

If you want to use selection slicer, I suggest you to create an unrelated calendar table to help your calculation.

Data model:

Measure:

Rolling 3 Month = 
VAR _SELECTION = MAX('Calendar'[Date])
RETURN
IF(MAX('Table'[Date])<=_SELECTION,
CALCULATE (
    SUM('Table'[Value]),
    FILTER(ALL('Table'), 'Table'[Date] >=EOMONTH(MAX('Table'[Date]),-3)+1&& 'Table'[Date]<=MAX('Table'[Date]))
))

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JoeBarry]

Hi @Craig23 

Why not create a Rolling 3 month measure?

Last 3 months Amount = 
CALCULATE (
    [MyMeasure],
    DATESINPERIOD ( 'DIM Date'[Date], MAX ( 'DIM Date'[Date] ), -3, MONTH )
)

Thanks

Joe

If this post helps, then please Accept it as the solution