Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Week Filter

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 col...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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)
v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
6 years ago

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!
  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    thank you so much! 

     

    this did the trick nicely, really appreciated!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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