Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RadiA31
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
davehus
Memorable Member
Memorable 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

View solution in original post

v-jiascu-msft
Employee
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

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.

View solution in original post

4 REPLIES 4
v-jiascu-msft
Employee
Employee

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.
v-jiascu-msft
Employee
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

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.
davehus
Memorable Member
Memorable 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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.