Cumulative total per month over years

[terdudov2]

Hello, 

I have a formula for cummulative total by month (so if I filter month2, it shows value of month1+month2 etc). It works perfectly fine for data 2022 (I have also tried using date instead of month, but the month worked better for me). However now there is also data for 2023. If I only filter year 2023, it starts calculating cumulative value from 1.1.2023, however, I would like to carry on the balances from 2022 also. Is there any chance to just adjust the formula I use to reach that? Thank you

[terdudov2]

@FreemanZ also I shoudl probably say, it works and calculates correct, if I do not filter year or month. But i need these filters for other visuals as well and need them to work

[terdudov2]

@FreemanZ Hello, thanks alot, I tried this, but does not work for me either. It shows no value for months where there is no record, plus I need to have a filter there for "month", which does not work either

[terdudov2]

@amitchandak Hello, thanks alot but this does not work in my model. Fox example it does not show any value in months without records. Also I need to use the "month" filter in my report and I cannot use "month" from the date table, because there is no way to make a relation between months (woudl  be M:N relation)

thank you

[FreemanZ]

hi @terdudov2 

As amitchandak mentioned, in case of running total cross years, YYYYMM is needed. try to:

1) add a YYYYMM column in your Journal table or in the Date Table. I tried with the date table

2) write a measure like:

AmtRT = 
CALCULATE(
    SUM(TableName[Amt]),
    DateTable[YYYYMM]<=MAX(DateTable[YYYYMM])
)

it worked like this:

sample dataset:

datetable:

result:

[FreemanZ]

Hi @terdudov2 

if you insist to do without a date table. try to:

1) add a column in your Journal table like:

YYYYMM2 = FORMAT([Date], "YYYYMM")

2) plot a visual with the YYYYMM2 column and a measure like this:

AmtRT2 = 
CALCULATE(
    SUM(TableName[Amt]),
    TableName[YYYYMM2]<=MAX(TableName[YYYYMM2])
)

it worked like this:

sample dataset:

[amitchandak]

@terdudov2 , Create a date using month year, and then with help from date table

date([Year],[Month],1)

Or separate year, month table can also do with YYYYMM column

example measure without using Window

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))

Running Total/ Cumulative: https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=41

Prefer Window function

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc