Forum Discussion
Category with specific dates
Hello I am trying to create a measure that checks weeks left to refresh for specific category in Level5 column. This is the table.
This is Weeks to refresh measure.
I am trying to modify measure since it needs to have specific refresh date for each Level 5 category. For level 5 categories that are in the table refresh dates are Female = 2022/06/20, Open = 2022/07/25, Wedding = 2022/03/28. At the moment Weeks to Refresh measure is just for one date and nor categories specific. Idea for measure is something like this. DATEDIFF(IF(Level 5 = Open,(today()-1, date(2022,07,25),WEEK),IF(Level 5 = Female,(today()-1, date(2022,06,20),WEEK),IF(Level 5 = Wedding,(today()-1, date(2022,03,28),WEEK). Expected results Female = 13 weeks, Open = 18 weeks, Wedding = 1 week.
Let me know if there is need for any additional information. Thanks.
Ah, OK. Try something like
Weeks to refresh = var currentCategory = SELECTEDVALUE('Table'[Category 5]) var refreshDate = SWITCH( currentCategory, "Open", DATE(2022, 7, 25), "Female", DATE(2022, 6, 20) ) return DATEDIFF( TODAY()-1, refreshDate, WEEK)
3 Replies
Create a new table with the Category and the refresh date. You can then use RELATED or LOOKUPVALUE to get the target refresh date and do a datediff with today
Due to Live connection method I am only able to use measures. I cant use calculated columns or create tables.
Ah, OK. Try something like
Weeks to refresh = var currentCategory = SELECTEDVALUE('Table'[Category 5]) var refreshDate = SWITCH( currentCategory, "Open", DATE(2022, 7, 25), "Female", DATE(2022, 6, 20) ) return DATEDIFF( TODAY()-1, refreshDate, WEEK)
