Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm trying to compute a caluculated column in the Calendar to show which period is current, past of future. Can't get my head around it how to do this.
The difficulty is that I need my calendar to be of week and period granularity, not on date level. I do have a Week Start Date.
Periods are sections of weeks. Sometimes 4 weeks, sometimes 5. Not much logic there.
We are now in Period 7, and I did manage to write a DAX that tells me that this week, we are in the current period. See screenshot below.
Next I need to do some sort of lookup.
Your help is much appreciated!
Solved! Go to Solution.
Hi @Steven-conance ,
According to your screenshot, I think we can determind [Period] based on [Week Start Date]. My code is based on this logic, we will calculate week start date based on today and then get period. Then use this dynamic period in switch.
Please try it to create a calculated column.
Passed/Current/Future Periods =
VAR _WeekStart_Date =
TODAY () - WEEKDAY ( TODAY (), 2 ) + 1
VAR _Period =
CALCULATE (
SUM ( 'TableName'[Period] ),
FILTER ( 'TableName', 'TableName'[Week Date Start] = _WeekStart_Date )
)
RETURN
SWITCH (
TRUE (),
[Period] > _Period, "Future",
[Period] = _Period, "This Period",
[Period] < _Period, "Past"
)
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-rzhou-msft not actually it's a 445 rythm. First period is 4 weeks, second period is 4 week, third period is 5 weeks.
And then x4 for a full year
Hi @Steven-conance ,
According to your screenshot, I think we can determind [Period] based on [Week Start Date]. My code is based on this logic, we will calculate week start date based on today and then get period. Then use this dynamic period in switch.
Please try it to create a calculated column.
Passed/Current/Future Periods =
VAR _WeekStart_Date =
TODAY () - WEEKDAY ( TODAY (), 2 ) + 1
VAR _Period =
CALCULATE (
SUM ( 'TableName'[Period] ),
FILTER ( 'TableName', 'TableName'[Week Date Start] = _WeekStart_Date )
)
RETURN
SWITCH (
TRUE (),
[Period] > _Period, "Future",
[Period] = _Period, "This Period",
[Period] < _Period, "Past"
)
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks @v-rzhou-msft, works like a charm. Today a new period started, and it switched exactly as intended. Thanks!
In essence, yes. But how to make this dynamic? When we're in Period 8, it's supposed to change. Then Period 8 is the current period. And so on
@Steven-conance , try like
New column =
var _max = maxx(filter(Table, Table[Date] = Today()) , [Week] )
return
Switch( True() ,
_max >7 , "Future",
_max = 7 , "This Period",
_max < 7 , "Past")
Default Date Today/ This Month / This Year: https://www.youtube.com/watch?v=hfn05preQYA
Thanks @amitchandak but again the question remains. The "7" here will be a different number every 4 weeks, so how to make that dynamic?
Hi @Steven-conance ,
Do you mean that period will increase every 4 weeks? Current period equal to 7 and after 4 weeks period equal to 8?
You can try this code to create a calculate column. We can get Period dynamiclly.
Passed/Current/Future Periods =
VAR _WeekStart_Date =
TODAY () - WEEKDAY ( TODAY (), 2 ) + 1
VAR _Period =
CALCULATE (
SUM ( 'TableName'[Period] ),
FILTER ( 'TableName', 'TableName'[Week Date Start] = _WeekStart_Date )
)
RETURN
SWITCH (
TRUE (),
[Period] > _Period, "Future",
[Period] = _Period, "This Period",
[Period] < _Period, "Past"
)
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Steven-conance , to me=e it seems like that does not need lookup. a new column
Switch( True() ,
[Week] >7 , "Future",
[Week] = 7 , "This Period",
[Week] < 7 , "Past")
User | Count |
---|---|
104 | |
92 | |
88 | |
78 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |