cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

Chart to show 3 months if data by selected date

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.

1 ACCEPTED SOLUTION
Community Support

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.

2 REPLIES 2
Community Support

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.

Solution Sage

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