DAX code assistance please:

[Tony_Singh]

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 write a formula for the 14 day rolling total from which to calculate the rolling average.

The following formula only returns a rolling 1 day average:

Rolling Budget = CALCULATE(sum(INFEED[BUDGET]),DATESINPERIOD('INFEED'[DATE].[Date],LASTDATE('INFEED'[DATE].[Date]),14,DAY))

With thanks & kind regards, Singh,

[tamerj1]

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]

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 · Accepted solution]

@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 ()
    )

[Greg_Deckler]

@Tony_Singh Try:

Rolling Average Measure =
  VAR __MaxDate = MAX('DATE'[Date])
  VAR __MinDate = __MaxDate - 14
RETURN
  SUMX(FILTER('INFEED',[Date] >= __MinDate && [Date] <= __MaxDate),[BUDGET])

[Tony_Singh]

Hi Greg, as I am newbie please do not take any offence this works as a caluclated measure however how would i write that as a column command, kind regards & thanks, Singh,

[Tony_Singh]

Hi Greg, I have looked in the book also, I have the first edition, still struggling with the technicality, I have copied and pasted the code from your reply into the formula bar and I get a value of 1875 which is 15*125, the 125 is the last daily value for the daily budget ie 31/12/2024,