Advance Date Filter in DAX

[Anonymous]

Hi All I have an issue I'm trying to solve. 

I have a set of gym members who have personal trainers. The members have dates that they activated and deactivated their memberships. I want to be able to calculate the number of active members a trainer had in a particular week.

For example, the previous week ran from 28 Oct - 03 November (Mon-Sun) and to get what I need I'd use the advance visual level filter and set the Date Active to "on or before" 03 Nov and "Is Blank". I'd add another advance visual level filter and set the Date In Active to "on or after" 28 Oct or "is blank". 

Using this method gives what I want but I need this to be done automatically because I'd have to manually update the filters at the beginning of a new week which is not efficient. Any thoughts on how I can achieve the same results using DAX?

Here's the link to a demo pbix file: https://drive.google.com/open?id=18G7TNnrD1zNbQWfrhotYYoCxWoqaKI6Q

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

Please check following steps as below:

1. Create calculated column:

     isactive_lastweek =

     IF (

         ISBLANK ( 'Table'[DateInactivated] ),

         "Yes",

         IF (

             WEEKNUM ( 'Table'[DateInactivated], 2 ) + 1

                 >= WEEKNUM ( NOW (), 2 ),

             "Yes",

             "No"

         )

     )

2. Add filter to visual:

3. Result would be shown as below:

Pbix as attached, hopefully works for you.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-jayw-msft I have one issue with this solution that I just stumbled upon, week number does not take into account the year the week number is in. If I say week number must be grater than or equal to current week number, then data from the last years week number will get filtered out. Is there a way around this?

[Anonymous]

@v-jayw-msft thank you so much for this solution, it works perfectly. The brilliance of this is that it actually asks the right question, "Was the member active last week?", which is something I overlooked.