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

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    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's avatar
      Tony_Singh
      New Member
      3 years ago

      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's avatar
        Tony_Singh
        New Member
        3 years ago

        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, 

         

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