topic DAX code assistance please: in DAX Commands and Tips

DAX code assistance please:

Tony_Singh — Wed, 19 Oct 2022 10:47:54 GMT

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,

Re: DAX code assistance please:

Greg_Deckler — Wed, 19 Oct 2022 11:19:43 GMT

Tony_Singh Try:

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

Re: DAX code assistance please:

Tony_Singh — Wed, 19 Oct 2022 11:38:25 GMT

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,

Re: DAX code assistance please:

Tony_Singh — Wed, 19 Oct 2022 12:02:18 GMT

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,

Re: DAX code assistance please:

tamerj1 — Wed, 19 Oct 2022 14:24:01 GMT

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 )

Re: DAX code assistance please:

Tony_Singh — Wed, 19 Oct 2022 14:46:27 GMT

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,

Re: DAX code assistance please:

tamerj1 — Wed, 19 Oct 2022 18:33:16 GMT

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