Forum Discussion

Steven-conance's avatar
Steven-conance
Helper I
4 years ago
Solved

Calculated column using 'lookup' in same table

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

Steven-conance's avatar
Steven-conance
Helper I
4 years ago

Anonymous 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.

    • Steven-conance's avatar
      Steven-conance
      Helper I
      4 years ago

      Thanks Anonymous, works like a charm. Today a new period started, and it switched exactly as intended. Thanks!