Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Craig23
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?

.Filter.PNGChart.PNG 

 

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
v-rzhou-msft
Community Support
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:

vrzhoumsft_0-1695611941481.png

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.

vrzhoumsft_1-1695611979617.png

 

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.

 

View solution in original post

2 REPLIES 2
v-rzhou-msft
Community Support
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:

vrzhoumsft_0-1695611941481.png

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.

vrzhoumsft_1-1695611979617.png

 

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
Solution Sage
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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.