Forum Discussion
Calculated column using 'lookup' in same table
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.