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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
mist1337
Regular Visitor

Running total line chart from multiple tables (one for each year)

Hello,

 

I'm total beginner in Power BI and need a bit of help making a line chart showing running total for three different years. Also sorry for the language barrier, since it's difficult for me to explain the problem.

 

I have three tables (one for each year). Example:

mist1337_0-1687431093687.png

 

I have crated a new measure in each of the years and I got it to show correctly but only for one year. And it depends from which table I choose "Month Nr." for x-axis.

 

DAX example for one of the years:

2021 =
    CALCULATE(
        SUM('Table_Projects21'[ZNESEK NAROČILA]),
        FILTER(
            ALL('Table_Projects21'),
            'Table_Projects21'[MESEC] <= MAX('Table_Projects21'[MESEC])
        )
    )

 

mist1337_2-1687431911832.png

 

Any help appreciated. Thanks in advance.

 

Best regards,

Nik

 

1 ACCEPTED SOLUTION
Ritaf1983
Super User
Super User

Hi @mist1337 

It seems you need to modify your model.
1. Union your three tables to 1 in PQ.

use this tutorial:

https://learn.microsoft.com/en-us/power-query/append-queries

2. Use a calendar table
Link to tutorial:

https://www.red-gate.com/simple-talk/databases/sql-server/bi-sql-server/using-calendars-and-dates-in...

3. calculate running total for the year for example in the way which showed here:

https://www.youtube.com/watch?v=WNMwTCtjeZc
And just put your year as a legend on a line graph

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

1 REPLY 1
Ritaf1983
Super User
Super User

Hi @mist1337 

It seems you need to modify your model.
1. Union your three tables to 1 in PQ.

use this tutorial:

https://learn.microsoft.com/en-us/power-query/append-queries

2. Use a calendar table
Link to tutorial:

https://www.red-gate.com/simple-talk/databases/sql-server/bi-sql-server/using-calendars-and-dates-in...

3. calculate running total for the year for example in the way which showed here:

https://www.youtube.com/watch?v=WNMwTCtjeZc
And just put your year as a legend on a line graph

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!