Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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)
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)
User | Count |
---|---|
99 | |
86 | |
78 | |
75 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |