Forum Discussion

NGC48's avatar
NGC48
Helper I
2 years ago

DAX Running Totals

I'm not sure if I have missed this somewhere but is it possible to create a DAX running total ignoring 'Weekends'? Using the Northwind Odata as an example, could an RT be calculated against the Freig...
General Comment
Need Help
Tips and Tricks
gmsamborn's avatar
gmsamborn
Super User
2 years ago

Hi NGC48 

 

I created a calculated column in my date table.

 

WorkDay Offset = 
VAR _d = NETWORKDAYS( TODAY(), 'Date'[Date], 1 ) - 1
RETURN
    IF( _d < 0, _d + 2, _d )

 

 

After creating a 1:* relationship between 'Date'[Date] and 'Sales'[Date], I created these measures.

 

Freight = SUM( 'Sales'[Freight] )

60 weekdays = 
VAR _Offset = SELECTEDVALUE( 'Date'[WorkDay Offset] )
VAR _Result =
    CALCULATE(
        MAX( 'Date'[Date] ),
        FILTER(
            ALL( 'Date' ),
            'Date'[WorkDay Offset] = _Offset + 60
        )
    )
RETURN
    _Result

Freight 60 = 
VAR _Start = SELECTEDVALUE( 'Date'[Date] )
VAR _End = [60 weekdays]
VAR _Result =
    CALCULATE(
        SUM( 'Sales'[Freight] ),
        DATESBETWEEN(
            'Date'[Date],
            _Start,
            _End
        )
    )
RETURN
    _Result

 

Let me know if this helps.

 

Running Freight Total for 60 weekdays.pbix

 

NGC48's avatar
NGC48
Helper I
2 years ago

Thansk gmsamborn. Trying to create a cumualative RT.  Your sample data includes Freight cost for 'weekends'. In my data (Sat,Sun) would not have any values as they are non-operational days. But has given me food for thought.

Regards.