Intra-year cumulative chart by week

[RadiA31]

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!

[davehus · Accepted solution]

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]

Hi @RadiA31,

Could you mark the proper answer as a solution please?

Best Regards,

Dale

[v-jiascu-msft · Accepted solution]

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

[davehus · Accepted solution]

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

[RadiA31]

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.