topic Re: DAX Rolling WoW with Week Numbers (no Dates) in DAX Commands and Tips

DAX Rolling WoW with Week Numbers (no Dates)

lfrosales — Fri, 31 May 2019 00:43:52 GMT

Hi All,

I am trying to do something in DAX that I can easily do with Excel (show value as difference from previous "x"). In this case, I have a column of Week Numbers with their respective Widget Counts for that week number. In Excel, I can drop into the pivot table and ask it to show me the value difference from the previous week number (regardless of whether it's a date or not). Image below:

I tried something to the effect of:

WoW =

VAR LastWeekCount =

SUMX(

FILTER(

'Query1',

'Query1'[WeekNum] = 'Query1'[WeekNum]-1

)

, 'Query1'[Widgets])

VAR fCount = Query1[Widgets]

RETURN fCount - LastWeekCount

Can someone help me convert a very easy Excel feature (show value as difference based on previous "x") to a seemingly obvious DAX statement that I can't quite figure out?

Thanks in advance as always!

Re: DAX Rolling WoW with Week Numbers (no Dates)

parry2k — Fri, 31 May 2019 01:36:24 GMT

lfrosales add following measure

WoW Widget = 
VAR __prevWeek = MAX( Table1[Week] ) - 1
VAR __thisWeekWidget = SUM( Table1[Widgets] )
VAR __prevWeekWidget = CALCULATE( SUM( Table1[Widgets] ), Table1[Week] =  __prevWeek ) 
RETURN __thisWeekWidget - __prevWeekWidget

Re: DAX Rolling WoW with Week Numbers (no Dates)

lfrosales — Fri, 31 May 2019 02:40:20 GMT

Thank you so much! I was struggling a couple hours on something that seemed super straightforward and you proved that it was! In my delirium, I accidentally tried adding it as a calculated column and wasn't having any luck, then I realized that you said as a measure! Worked like a charm! The only quirk I will try to work out is that it looks at week 1 of the year and gives it a value equal to that week's widget count. I will try to figure out a way to tell it to ignore Week 1 and continue with the rest.

See below: