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