Forum Discussion
last week number as a slicer
Hi All ,
I need to create a report which procides compliance % for last week.
For this I need help to create a dynamic filter ("last promise week #" here). I am not able to use MAX as date syntax .
any help, very much appreciated!
Thanks,
9 Replies
- lbendlin
Super User
Do you have a week number column in your Calendar/Dates table?
- lbendlin
A Calendar/Dates table requires contiguous dates across your entire fact date range plus a little more depending on what date math you are doing. "promise week" is a fact table attribute. Required, but not sufficient. Can you show your data model?
- mahoneypat
Microsoft Employee
If you add a Date table, you can make a calculated column like this one to identify the current week. You can then use it in your measure to filter to just that week (IsCurrentWeek =1). Or you can use it in the slicer filter.
IsCurrentWeek = var weekstart = TODAY() - WEEKDAY(TODAY()) +1var weekstop = weekstart +6return IF('Date'[Date]>=weekstart && 'Date'[Date] <=weekstop, 1, 0)If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Hi mahoneypat , I do have a date table in my model. However, for the report , I need the last promise week # as the end result. A promise week # can have two or more dates included .
Also, should a relationship be created with the date table you mentioned for the syntax to work?
I tried creating this syntax:
Thanks,
- mahoneypat
From the picture of your model, your date table was not obvious. From the pic in your last post, the FILTER function returns a table so can't be used in the return of your measure. You need to also do an aggregation to return a scalar result.
Regards,
Pat
