Cumulative total by year

[ef3f]

I need to create a matrix of revenue by month and year, with each year showing a running total. Essentially:

I have a date table linked to my sales data and created the following measure for cumulative sales:

Cumulative = CALCULATE(sum('TTM Data'[Extended Price]), filter(ALL('Date Table'[Date]), 'Date Table'[Date]<=max('Date Table'[Date])))

When I use the date field from my date table as the row and column fields, I get a matrix that does not reset the cumulative total at each year:

How should I write the Cumulative measure to correct this and give me what I want?

Thanks in advance for the help!

[Ashish_Mathur]

Hi,

To your visual, drag Year and Month from the Date Table.  Write these measures

EP = sum('TTM Data'[Extended Price])

EP YTD = CALCULATE([EP],DATESYTD('Date Table'[Date],"31/12"))

Drag the second measure to the visual.

Hope this helps.

[amitchandak · Accepted solution]

Try like

Totalytd Sales = CALCULATE(TOTALYTD(sum(TTM Data[Extended Price]),'Date Table'[Date],"12/31"))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[lc_finance]

Hi @ef3f ,

I think what you need is the TOTALYTD formula: https://docs.microsoft.com/en-us/dax/totalytd-function-dax

In your formula, you are filtering all dates earlier than the current date.

This means that in March 2019 you will include Jan, Feb 2019 but also all months of 2018 and 2017.

TOTALYTD should do exactly what you want.

Let me know if this helps you.

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com