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

8 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Community 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!
    • 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

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    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)
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you so much, this worked!

       

      Much appreciated.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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)

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Solved:

     

    UAPs Prior Week = VAR __PreviousWeek = MAX('FactNELWeekly'[Previous Week No.])
    return
    CALCULATE([UAP's],FactNELWeekly[Week] = __PreviousWeek)
     
    Thanks to all who contributed!