Need help creating 5 year history graph

[ahassan]

How would you recreate this graph below?  Particularly the 5 year grey band that represents the 5 year range?  Also sorting the x axis by month day has been an issue I've been running into, any ideas?

[v-xuxinyi-msft · Accepted solution]

Hi @ahassan 

Based on the information you provided, I ran the following tests:

My sample:

Table = CALENDAR(DATE(2017,1,1), DATE(2023,12,31))

value = (RAND() + 1) * 100

1. create several measures as shown below.

5 Year Range = CALCULATE(SUM('Table'[value]),ALLEXCEPT('Table','Table'[Date].[Month]))

5 Year Average = DIVIDE([5 Year Range],5)

2022 = CALCULATE(SUM('Table'[value]), FILTER('Table', YEAR('Table'[Date]) = 2022))

2023 = CALCULATE(SUM('Table'[value]), FILTER('Table', YEAR('Table'[Date]) = 2023))

2. create the visual as shown below

You can modify the effect you want with the options shown in the screenshot.

This is my setting.

Is this the result you expect?

If I've misunderstood you, please provide detailed sample data and the results you are hoping for. We can better understand the problem and help you. Show it as a screenshot or excel. Please remove any sensitive data in advance.

Best Regards,
Community Support Team _Yuliax
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-xuxinyi-msft · Accepted solution]

Hi @ahassan 

Based on the information you provided, I ran the following tests:

My sample:

Table = CALENDAR(DATE(2017,1,1), DATE(2023,12,31))

value = (RAND() + 1) * 100

1. create several measures as shown below.

5 Year Range = CALCULATE(SUM('Table'[value]),ALLEXCEPT('Table','Table'[Date].[Month]))

5 Year Average = DIVIDE([5 Year Range],5)

2022 = CALCULATE(SUM('Table'[value]), FILTER('Table', YEAR('Table'[Date]) = 2022))

2023 = CALCULATE(SUM('Table'[value]), FILTER('Table', YEAR('Table'[Date]) = 2023))

2. create the visual as shown below

You can modify the effect you want with the options shown in the screenshot.

This is my setting.

Is this the result you expect?

If I've misunderstood you, please provide detailed sample data and the results you are hoping for. We can better understand the problem and help you. Show it as a screenshot or excel. Please remove any sensitive data in advance.

Best Regards,
Community Support Team _Yuliax
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ahassan]

Looks like your answer calculates the sum for each month, but I need it for month day.  For the example, instead of the sum of March, I need the sum of March 3rd

[v-xuxinyi-msft · Accepted solution]

Hi @ahassan

You can modify the following formula:

5 Year Range = CALCULATE(SUM('Table'[value]),ALLEXCEPT('Table', 'Table'[Date]))

If I've misunderstood you, please provide detailed sample data and the results you are hoping for. We can better understand the problem and help you. Show it as a screenshot or excel. Please remove any sensitive data in advance.

Best Regards,
Community Support Team _Yuliax