Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Running Totals per Month

Hi,

 

I can't seem to run my running totals properly when using the date table (Relationship between First Transaction Date and Date in Date Table) and x-axis categorical type, but functions okay when used with a "Continuous" X-Axis using the First Transaction Date on the Transaction Table.

Continuous using First Transaction Date from Transaction Table:

Categorical using Date from Date Table: 

 

Here's the current code:

Running Total Dragonpay Users =
CALCULATE(
    COUNTA('Dragonpay Transactions'[Customer#]),
    FILTER(
        ALLSELECTED('Dragonpay Transactions'[First Transaction Date]),
        ISONORAFTER('Dragonpay Transactions'[First Transaction Date], MAX('Dragonpay Transactions'[First Transaction Date]), DESC)
    )
)
 
I need to be able to summarize the running total in such a way it only shows the running total movement per month, and not per data point. Thank you!
Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

    Anonymous , You can have running total like

     

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

     

    if you want it to be reset at the month level

     

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

2 Replies

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

    Anonymous , You can have running total like

     

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

     

    if you want it to be reset at the month level

     

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))