Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Relative Dates - more options

Hi

I woudl like to use the releitive date filter to say is not in the last calendar week (for example), but I can only filter on inclusions, not exclusions. 

 

Does anyone know if there is a way to create a flag from a date field in DAX so that I can use that in the filter instead?

I can't used advanced filtering as this means I would have to go and update the date each week. 

 

I am not in a position to amend or create anything in Power Query.

 

thanks in advance for your suggestions.

H

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi, no it's not to use in a date slicer. I have a card that holds a total of appointments that show as having a planned date for the previous week, I can use a differnt field filter on these appointments to show what was actually attended using relative date in the last calendar week, but if i want to create a card that shows appointments that were planned for the previous week but were not attended in the previous week, relavtive date filter doesn't allow this.

    • v-chenwuz-msft's avatar
      v-chenwuz-msft
      Community Support
      4 years ago

      Hi Anonymous ,

       

      Maybe you can just create a measure to calculate the result via 

      not attend =
      var _lastweekstart = today() - weekday(TODAY(),2)-7
      var _lastweekend = _lastweekstart+6
      return
      COUNTROWS(FILTER('calendar',[Date]>=_lastweekstart&&[Date]<=_lastweekend &&[status]="not attended"))
       

      Best Regards

      Community Support Team _ chenwu zhu

       

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi,
        Thanks for this. there is no field to hold a status of "not attended"
        I have a table that shows when appointments were scheduled to be attended, and another table that shows when they were attended.
        Appointments maybe scheduled many times, but only attended once.
        The question I wish to answer is how many were scheduled for last week, but not attended (because they were rescheduled).
        scheduled date in last week, attended date not in last week.