Running Total Per Month

[sabd80]

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?

[sabd80 · Accepted solution]

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.

)

[Ashish_Mathur]

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]

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

[Ashish_Mathur]

Hi,

See if this measure works

Quantity shipped YTD = calculate([quantity shipped],datesbetween(calendar[date],minx(allselected(),Calendar[Date]),max(calendar[date])))

[sabd80]

there is something wrong with syntax inside minx.

[grandtotal]

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]

@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

[grandtotal]

can you post the definiton of [Quantity Shipped]?

[sabd80]

Quantity Shipped = SUM('Sales Order Details'[Unit Quantity Shipped])