cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
RadiA31 Frequent Visitor
Frequent Visitor

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).

 

 

cumulative-weedspray.PNG

 

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:

2graphs.PNG

 

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!

2 ACCEPTED SOLUTIONS

Accepted Solutions
davehus Member
Member

Re: Intra-year cumulative chart by week

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
Community Support Team
Community Support Team

Re: Intra-year cumulative chart by week

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
davehus Member
Member

Re: Intra-year cumulative chart by week

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
Community Support Team
Community Support Team

Re: Intra-year cumulative chart by week

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

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

Re: Intra-year cumulative chart by week

Hi @RadiA31,

 

Could you mark the proper answer as a solution please?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
RadiA31 Frequent Visitor
Frequent Visitor

Re: Intra-year cumulative chart by week

Thank you @davehus very helpful videos from Enterprise DNA! That's actually where I got my formula in the first place. I will definitely follow their channel.