Cumulative Stacked Line Chart - YoY by Month

[biotechanalyst]

Hello pbi community. Hoping you can help me out with the technicle feasibility of a cumulative line chart Yoy stacked by month.

Im trying to show a comparison of the cumulative running total of supplier spend (measure = GBP Spend) over the year (YTD = 2022), and compare that to the previous calendar years (PY = 2021, PPY = 2020) over the same months.

I have a invoice date heirarchy in the data model, splitting invoice day, month and year into seperate columns. 

To get the cumulative running total for each year, I used the following dax code (used for each YTD, PY, PPY):  

PY Cumulative GBP =

CALCULATE (

[PY GBP SEND],

FILTER (

ALLSELECTED('Vendor Spend Summary'),

'Vendor Spend Summary'[Invoice Date] <= MAX ('Vendor Spend Summary'[Invoice Date])))

My table correctly sums cumulatively for the year, but repeats the total for the following year months (total cumulative for 2020 repeats for each month in 2021). This causes the visual to display the total cumulative value for the year for those months for PPY and PY. YTD measure works as attended - showing the cumulative running total of spend throughout the year.

 

Please let me know how i can upload a sample excel data file of this screenshot. Thank you for considering!

[v-henryk-mstf]

Hi @biotechanalyst ,

It sounds like you are using the same measure for the previous years and the current year, but the measure is only designed to work for the current year. To fix this issue, you can create separate measures for each of the years, and use a different filter for each measure to only include invoices up to the end of that year. For example, the measure for the current year could use the following code:

YTD Cumulative GBP =
CALCULATE (
    [YTD GBP Spend],
    FILTER (
        ALLSELECTED ( 'Vendor Spend Summary' ),
        'Vendor Spend Summary'[Invoice Date]
            <= MAX ( 'Vendor Spend Summary'[Invoice Date] )
    )
)

For the previous year, you would use a similar measure, but with a filter that only includes invoices up to the end of the previous year. For example:

PY Cumulative GBP =
CALCULATE (
    [PY GBP Spend],
    FILTER (
        ALLSELECTED ( 'Vendor Spend Summary' ),
        'Vendor Spend Summary'[Invoice Date]
            <= MAX ( 'Vendor Spend Summary'[Invoice Date] - 1, [Invoice Date].[Year] )
    )
)

This measure uses the MAX function to find the maximum invoice date for the previous year, and then filters the table to only include invoices up to that date. This will give you the cumulative total for the previous year, without including any invoices from the current or future years. You can use a similar approach for the previous previous year (PPY) as well.

By the way, you can provide test data refer to below link:

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-... 

Best Regards,
Henry

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

[biotechanalyst]

For the PY measure, is there a different way to filter that only includes invoices for this year?  Im having trouble making this calculation work in the model.

<= MAX ( 'Vendor Spend Summary'[Invoice Date] - 1, [Invoice Date].[Year] )

I do not have the ability to select "[Invoice Date]. [Year]"

For the [PY GBP Spend] Measure, I use a filter like this that only sums invoices for that year (2021), and works in the table (column PY GBP)

PY GBP = CALCULATE([GBP Spend],FILTER('Vendor Spend Summary',YEAR('Vendor Spend Summary'[Invoice Date])=(Year(TODAY())-1)))