Week Filter

[Anonymous]

Hi

Does anyone know why this DAX doesnt work...

Prior Week = CALCULATE([UAP's],FactNELWeekly[Previous Week No.])

UAPs = count distict measure, works fine.

Previous Week No. is a number column, in this case "8"

I have a weekly aggregated table, so i dont have a date column, just weekkey and week number, which is fine in this case, although not the usual way i model...actaully i count convert the below into a date format and wasnt sure if i need to.

All im lookiing to do is take this week's UAP figure, last weeks UAP figure, and minus them to get the WoW. But my DAX above doesnt seem to like filtering on a week number (created a column for previous week which was simply Week-1)

Example of my data table below:

Any sugguestions would be much appreciated please. 

Thanking you in advance

Ben

[Anonymous]

Solved:

UAPs Prior Week = VAR __PreviousWeek = MAX('FactNELWeekly'[Previous Week No.])

return

CALCULATE([UAP's],FactNELWeekly[Week] = __PreviousWeek)

Thanks to all who contributed!

[v-kelly-msft]

Hi @Anonymous ，

Based on @Greg_Deckler 's suggestion,the measure needs to be corrected as below:

Prior Week =
  VAR __PreviousWeek = MAX('FactNELWeekly'[Previous Week No.])
RETURN
  CALCULATE([UAP's],FILTER('FactNELWeekly','FactNELWeekly'[Week] = __PreviousWeek)

 WoW=[UPA's]-_PreviousWeek

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi Kelly @v-kelly-msft 

I have an additional question on this topic, everything works with the DAX above, so thanks againn for that. But im noticing there's a week 0 appearing, and actually I've replicated this for a monthly report and again, im getting a 0 month. Is there a way to take care of this scenario within the same Dax formula? I figured it would be linked to this formula, hence why im posting here.

I'm googling this now acutally, but thought i'd try my luck here at the same time :).

Thanks

Ben

[Anonymous]

thank you so much! 

this did the trick nicely, really appreciated!

[Greg_Deckler]

Thanks @v-kelly-msft ! That's what happens when I don't actually test my DAX, I mess up some syntax!

[Greg_Deckler · Accepted solution]

So, the way that I would do this in a measure would be something along the lines of:

Prior Week =
  VAR __PreviousWeek = MAX('FactNELWeekly'[Previous Week No.])
RETURN
  CALCULATE([UAP's],FILTER('FactNELWeekly'[Week] = __PreviousWeek)

[Anonymous]

Thank you so much, this worked!

Much appreciated.

[Anonymous]

HI,

It could be something like this (if it's a calculated column):

var previousWeek = FactNELWeekly[Previoud Week No.]

return
CALCULATE([UAP's],FactNELWeekly[Week No.] = previousWeek)