Cumulative Sum Line Chart Split by Legend

[newpbiuser01]

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

100	2021	Jan
150	2021	Feb
220	2021	Mar
10	2021	Apr
20	2022	Jan
50	2022	Feb
50	2022	Mar
100	2022	Apr
120	2023	Jan
150	2023	Feb
50	2023	Mar
150	2021	Jun
200	2021	Jul
12	2021	Aug
80	2021	Sep
60	2021	Oct
15	2021	Nov
20	2021	May
25	2022	Jun
35	2022	Jul
20	2022	Aug
10	2022	Sep
23	2022	Oct
20	2022	Nov
15	2022	May
10	2022	Dec
20	2021	Dec

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.  

[Mkarwa-123]

@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]

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. 😞

[Mkarwa-123]

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.

[newpbiuser01]

Hi @Mkarwa-123 , the Year-Month column is actually a date column formatted as a "yyyy-mm".