Forum Discussion

newpbiuser01's avatar
newpbiuser01
Helper V
3 years ago

Cumulative Sum Line Chart Split by Legend

Hello, 

I have a data table which shows spend by year and month. I am trying to create a line cumulative line chart that will show the spend as it goes up over time.

 

The data tables are as follows:
Spend Data:

SpendYearMonth

1002021Jan
1502021Feb
2202021Mar
102021Apr
202022Jan
502022Feb
502022Mar
1002022Apr
1202023Jan
1502023Feb
502023Mar
1502021Jun
2002021Jul
122021Aug
802021Sep
602021Oct
152021Nov
202021May
252022Jun
352022Jul
202022Aug
102022Sep
232022Oct
202022Nov
152022May
102022Dec
202021Dec

 

Date table: 

 

However,  I can't seem to get a line chart that will either limit the line for the year we have incomplete data for (e.g. for 2023 - we want the line to stop at March). So to get around it, I created a dimension date table (we don't have the data by dates, but by month), get the latest date and then create a measure to get the cumulative sum.  My measures are as follows:

 

LatestFYFM =

VAR _LatestYear = left(MAX('Date Table'[Year-Month]),4)
 VAR _END = CALCULATE(MAX('Date Table'[Month Number]), FILTER('Date Table',[Year]=_LatestYear))
RETURN  _LatestYear & "-" & _END
 
Cumulative Spend=
       CALCULATE(
           SUM('Spend Table'[Spend]),
          FILTER(ALL('Date Table'), 'Date Table'[Year-Month] <= [LatestFYFM])
)
 
Using these measures, I am able to get the following line chart which doesn't stop the year 2023 spend in March, and also won't give me cumulative sum. What am I doing wrong? I'd really appreciate any help!

Thank you.  
 

 

 
 

 

Need Help

4 Replies

  • Mkarwa-123's avatar
    Mkarwa-123
    Resolver II
    3 years ago

    You can add first date of the month e.g March, 2023 : 01-03-2023. Also create date table for dates not for month. Date table has to be contiguous dates table.

  • Mkarwa-123's avatar
    Mkarwa-123
    Resolver II
    3 years ago

    newpbiuser01 It should be date-month-year for date table. you try just make as date in both tables make a relationship on date column and your filters will be working hopefully.

    • newpbiuser01's avatar
      newpbiuser01
      Helper V
      3 years ago

      Hi Mkarwa-123 , I did that, I added the date(the 1st of each month) in the date table, and the spend table. Created a relaltionship between the two, but run into same issue. 😞