Forum Discussion

sabd80's avatar
sabd80
Helper IV
3 years ago
Solved

Running Total Per Month

Hi,

I want to get running total per day and month, most of the measures have filter on date like below:

 

filter(all('Date'),'Date'[date] <=max('Date'[date])

 

but when I do that the running total starts from the available data, not what is on the visual.

so I have to add another filter on the date.

 

this is my measure:

Testing Qty Shipped RT Day =
VAR SelectedDate = CALCULATE( min('Calendar - Requested Date'[Calendar Date]), ALLSELECTED('Calendar - Requested Date'[Calendar Date]))
return
CALCULATE([Quantity Shipped],
        FILTER(ALL('Calendar - Requested Date'),
                'Calendar - Requested Date'[Calendar Date]<= max('Calendar - Requested Date'[Calendar Date]) &&
                   'Calendar - Requested Date'[Calendar Date]>= SelectedDate
))
 
The other issue I have is when I add the month to the visual, the running total does not work, like below:
 

Do I need to create another measure for the monthly view?

Need Help
  • sabd80's avatar
    sabd80
    3 years ago

    I found this below dax somewherelse and it is doing what it should:

    Testing Qty Shipped RT =
    CALCULATE([Quantity Shipped],
           FILTER(ALLSELECTED('Calendar - Requested Date'), 'Calendar - Requested Date'[Calendar Date]<= max('Calendar - Requested Date'[Calendar Date]))
     
    Thanks for everyone's help.
           
    )

9 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    Follow this process

    1. Create a Calendar Table with calculated column formulas for Year, Month name and Month number

    2. Sort the Month name column by the Month number

    3. Create a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table

    4. To your visual, drag Year, Month name and Date from the Calendar Table

    5. Write these measures

    Quantity Shipped = SUM('Sales Order Details'[Unit Quantity Shipped])
    Quantity shipped YTD = calculate([quantity shipped],datesytd(calendar[date],"31/12"))

    Hope this helps.

    • sabd80's avatar
      sabd80
      Helper IV
      3 years ago

      Ashish_Mathur , thanks for your reply, I don't want YTD, I just need cummulative of the dates in the visuals.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        3 years ago

        Hi,

        See if this measure works

        Quantity shipped YTD = calculate([quantity shipped],datesbetween(calendar[date],minx(allselected(),Calendar[Date]),max(calendar[date])))
  • sabd80's avatar
    sabd80
    Helper IV
    3 years ago

    I found this below dax somewherelse and it is doing what it should:

    Testing Qty Shipped RT =
    CALCULATE([Quantity Shipped],
           FILTER(ALLSELECTED('Calendar - Requested Date'), 'Calendar - Requested Date'[Calendar Date]<= max('Calendar - Requested Date'[Calendar Date]))
     
    Thanks for everyone's help.
           
    )
  • grandtotal's avatar
    grandtotal
    Resolver III
    3 years ago

    You should use the concept described here by SQLBI:
    https://www.sqlbi.com/articles/computing-running-totals-in-dax/

     

    Sales RT :=
    VAR MaxDate = MAX ( 'Date'[Date] ) -- Saves the last visible date
    RETURN
        CALCULATE (
            [Sales Amount],           -- Computes sales amount
            'Date'[Date] <= MaxDate,  -- Where date is before the last visible date
            ALL ( Date )              -- Removes any other filters from Date
        )

     

    I hope this will help you!

    • sabd80's avatar
      sabd80
      Helper IV
      3 years ago

      grandtotal , I have tried that, it does not work for daily and monthly.

       

      Testing Qty Shipped RT Month2 =
      VAR SelectedDate =  max('Calendar - Requested Date'[Calendar Date])
      VAR QtyShippedRT=
      CALCULATE([Quantity Shipped],
              'Calendar - Requested Date'[Calendar Date]<= max('Calendar - Requested Date'[Calendar Date]), ALL('Calendar - Requested Date')
             
      )
      return
      QtyShippedRT