Forum Discussion

Tony_Singh's avatar
Tony_Singh
New Member
3 years ago
Solved

DAX code assistance please:

Hi there please would someone be able to assist with a 14 day rolling average query for a column calculation in PBI, Dates are dd/mm/yy and I have Currency data in the Budget column I am looking to ...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Tony_Singh 

    My mistake. Somehow I missed to remove the filters. 

    Rolling Budget =
VAR CurrentDate = 'INFEED'[DATE]
VAR PreviousDate = CurrentDate - 14
RETURN
    CALCULATE (
        SUM ( INFEED[BUDGET] ),
        'INFEED'[DATE] <= CurrentDate,
        'INFEED'[DATE] >= PreviousDate,
        REMOVEFILTERS ()
    )
tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi Tony_Singh 

please try

Rolling Budget =
VAR CurrentDate = 'INFEED'[DATE]
VAR PreviousDate = CurrentDate - 14
RETURN
    CALCULATE (
        SUM ( INFEED[BUDGET] ),
        'INFEED'[DATE] <= CurrentDate,
        'INFEED'[DATE] >= PreviousDate
    )
Tony_Singh's avatar
Tony_Singh
New Member
3 years ago

Hi there, it is only returning the single value ie the pickup value,

In essence we need to aggregate the first 14 days ie 1-14, then 2-15, then 3-16 and so on a bit of a dilema,

Any further suggestions?

Regards,

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Tony_Singh 

    My mistake. Somehow I missed to remove the filters. 

    Rolling Budget =
VAR CurrentDate = 'INFEED'[DATE]
VAR PreviousDate = CurrentDate - 14
RETURN
    CALCULATE (
        SUM ( INFEED[BUDGET] ),
        'INFEED'[DATE] <= CurrentDate,
        'INFEED'[DATE] >= PreviousDate,
        REMOVEFILTERS ()
    )