Cumulative Total with 2 parameter columns

[louie_c]

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

Sample File: Power BI Sample 

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:

[v-xiaotang]

Hi @louie_c 

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.

[louie_c]

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 

[amitchandak]

@louie_c , 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])))