Forum Discussion
Intra-year cumulative chart by week
Hi guys,
Power BI newbie here, really hoping you can help as I'm out of ideas!
I am trying to build a line chart with 2 lines to show intra-year trends regarding cumulative delay minutes (as below).
My X-axis in this case needs to be weeks 1-52 rather than dates.
The data from 2017 covers weeks 1-52 whereas data from 2018 covers 1-18 (to date, but will change as weeks go by). They both work great as separate graphs. My measure is:
= CALCULATE(
sum('2018 Delays'[Minutes]),
FILTER(
ALLSELECTED('2018 Delays'),
'2018 Delays'[Week] <= MAX('2018 Delays'[Week])
Same for 2017 data, just '2017 Delays'[Minutes] and '2017 Delays'[Week]
I created a relationship between the 2 Week columns through a general Week dataset with unique values 1-52.
When I try to put them both in a graph, the best I've gotten to is this:
Basically when the Axis is 2017[Week], the 2018 chart is not cumulative anymore and vice-versa.
And now I'm completely out of ideas. Please, help!
- Hi - Try the video below from Sam in Enterprise DNA. He has a number of videos on this subject and many more for someone new to Powerbi.
https://youtu.be/3TLlxoTkBn0
D Hi RadiA31,
Seems you need a new date table in your scenario. Then you can establish relationships between the date table and the other two tables. Please give it a try. If you need more details, please provide a sample of your data.
Best Regards,
Dale
4 Replies
davehusMemorable Member
Hi - Try the video below from Sam in Enterprise DNA. He has a number of videos on this subject and many more for someone new to Powerbi.
https://youtu.be/3TLlxoTkBn0
D- v-jiascu-msft
Microsoft Employee
Hi RadiA31,
Seems you need a new date table in your scenario. Then you can establish relationships between the date table and the other two tables. Please give it a try. If you need more details, please provide a sample of your data.
Best Regards,
Dale
- v-jiascu-msft
