topic Cumulative Total with 2 parameter columns in DAX Commands and Tips

Cumulative Total with 2 parameter columns

Anonymous — Fri, 27 May 2022 10:53:28 GMT

Asking for your kind help fellow Power BI Users. Thank you in advance for those who would take time to help 🙂

I am trying to have cumulative total per month with two other columns to consider (country and portfolio).

Need to have cumulative total for this month and last month.

Total is calculated using measures over DIM date, such as Date Filter say selects 21-Feb, table will show running total for Jan 1 to 25 in one column and Feb 1 to 21 on the next column.

DAX Measures Used:

Collections MTD (This Month) =

CALCULATE(SUM(DATA[Sales]),

FILTER(ALL(DATA[Date]),

DATA[Date] <= MAX('_Dim Date'[Date]) &&

DATA[Date] >= DATE(YEAR(MAX('_Dim Date'[Date])),MONTH(MAX('_Dim Date'[Date])),1) ) )

Collections MTD (Last Month) =

CALCULATE(SUM(DATA[Sales]),

FILTER(ALL(DATA[Date]),

DATA[Date] <= EDATE(MAX('_Dim Date'[Date]),-1) &&

DATA[Date] >= EDATE(DATE(YEAR(MAX('_Dim Date'[Date])),MONTH(MAX('_Dim Date'[Date])),1),-1) ) )

Sample screenshot, this one is the daily sales, need to add cumulative MTD sales:

Re: Cumulative Total with 2 parameter columns

amitchandak — Sat, 28 May 2022 00:37:05 GMT

Anonymous , You can use datesmtd for that

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

Or have a month Day columns and month Rank

Month Rank = RANKX(all('Date'),'Date'[Month Start date],,ASC,Dense)

Month Day = day([date])

measures

MTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank]) && [Month Day] <=max([Month Day])))
LMTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1 && [Month Day] <=max([Month Day])))

Re: Cumulative Total with 2 parameter columns

Anonymous — Sat, 28 May 2022 06:08:08 GMT

Thanks. I tried #1 and it seems still shows daily thing and not cumulative. I'm trying #2 but can't seem to get it work.

If I can indulge further to ask from your precious time to do the solution on this demo file? WIll really appreciate your kindness for this. 🙌

Power BI Sample

Re: Cumulative Total with 2 parameter columns

v-xiaotang — Tue, 31 May 2022 06:52:02 GMT

Hi Anonymous

I am a little confused... please check the pitture below.

Best Regards,

Community Support Team _Tang

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