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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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