topic Re: Chart to show 3 months if data by selected date in DAX Commands and Tips

Chart to show 3 months if data by selected date

Craig23 — Fri, 22 Sep 2023 06:02:11 GMT

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.

Re: Chart to show 3 months if data by selected date

JoeBarry — Fri, 22 Sep 2023 06:36:59 GMT

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

Re: Chart to show 3 months if data by selected date

Anonymous — Mon, 25 Sep 2023 03:20:08 GMT

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.