Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Cumulative Total with 2 parameter columns

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:

 

 

 

 

 

 

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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])))

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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 

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    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.