topic Re: Cumulative total per month over years in DAX Commands and Tips

Cumulative total per month over years

terdudov2 — Fri, 20 Jan 2023 13:20:20 GMT

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

Re: Cumulative total per month over years

amitchandak — Fri, 20 Jan 2023 13:22:51 GMT

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

Re: Cumulative total per month over years

terdudov2 — Mon, 23 Jan 2023 00:31:52 GMT

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

Re: Cumulative total per month over years

FreemanZ — Mon, 23 Jan 2023 02:37:07 GMT

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:

Re: Cumulative total per month over years

FreemanZ — Mon, 23 Jan 2023 02:36:30 GMT

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:

Re: Cumulative total per month over years

terdudov2 — Mon, 23 Jan 2023 20:30:22 GMT

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

Re: Cumulative total per month over years

terdudov2 — Mon, 23 Jan 2023 20:38:25 GMT

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