Tagging date column with current period tags

[Anonymous]

Hi All, 

Sorry in advance for the long post. I'm so stuck on this problem. 

I have a dataset (that i've attached) and i will use this as the basis of my "dimDate" table in Power BI (once i'm done it will connect to a database) but at the moment, i've just mocked up some data to show what i would potentially look like. 

Basically, the fortnight ends every 2nd and 4th Monday if that month has 4 mondays in it. 
If that month has 5 Mondays, then that fortnight will end on the 1st Monday, 3rd Monday, and 5th Monday. 

So from that fortnight ending column, it will include contiguous dates 14 days prior and including that fortnight end. 

What i now need to do is dynamically assign tags in that dimDate table that says "current period", "previous period" and "next period". 

"Current period" tag will be assigned to the fortnight that capture TODAY(). So for example, this month (August 2019), there are 4 Mondays in this month. So my current period would be "30 July 2019 - 12 August 2019". 
But this needs to changed dynamically based on TODAY(). 

Then "Previous Period" would tag the fortnight period that falls right before current period. 

Then "Next Period" tag would tag the fortnight period that falls right afrer current period. 

I'm still new to Power BI and i cannot seem to understand how to even start this especially because of the complexity of the fortnight ending dates. 

Thanks All. 

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

We can do that with some filtering on the table base on TODAY().  Each time the model is refreshed the value of TODAY() will get recalculated so the tagging will shift when you go from one period to another.

Period =
VAR RowFortnightStart = 'Table'[Fortnight Start Date]
VAR CurrentFortnightStart =
    CALCULATE (
        MAX ( 'Table'[Fortnight Start Date] ),
        FILTER (
            'Table',
            'Table'[Fortnight Start Date] <= TODAY ()
             && 'Table'[Fortnight End Date] >= TODAY ()
        )
    )
VAR NextFortnightStart =
    CALCULATE (
        MIN ( 'Table'[Fortnight Start Date] ),
        FILTER ( 'Table', 'Table'[Fortnight Start Date] > CurrentFortnightStart )
    )
VAR PrevFortnightStart =
    CALCULATE (
        MAX ( 'Table'[Fortnight Start Date] ),
        FILTER ( 'Table', 'Table'[Fortnight Start Date] < CurrentFortnightStart )
    )
RETURN
    SWITCH (
        TRUE (),
        RowFortnightStart = CurrentFortnightStart, "Current Period",
        RowFortnightStart = NextFortnightStart, "Next Period",
        RowFortnightStart = PrevFortnightStart, "Previos Period"
    )

I pasted your data into a .pbix file and created the period column on the table.  You can download the example file here.  Fortnight.pbix

[Anonymous]

THANK YOU SO SO MUCH! This is exactly what i was after. 

If you don't mind, could you walk me through the logic? 

Thanks again so much!

[jdbuchanan71]

Sure, it works something like this:

VAR CurrentFortnightStart =
    CALCULATE (
        MAX ( 'Table'[Fortnight Start Date] ),
        FILTER (
            'Table',
            'Table'[Fortnight Start Date] <= TODAY ()
             && 'Table'[Fortnight End Date] >= TODAY ()
        )
    )

This piece filters the table to just the rows where [Fortnight Start Date] <= TODAY and [Fortnight End Date] >= TODAY.  This give us a subset of the table with just 14 rows (7/30/2019 - 8/12/2019).  Then it pulls the MAX ( [Fortnight Start Date] ) from that 14 rows so we get 7/30/2019.

• CurrentFortnightStart = 7/30/2019

VAR NextFortnightStart =
    CALCULATE (
        MIN ( 'Table'[Fortnight Start Date] ),
        FILTER ( 'Table', 'Table'[Fortnight Start Date] > CurrentFortnightStart )
    )

This filters the table to just the rows where [fortnight Start Date] is > the 7/30/2019 we calculated in the previous step.  This give us a table all the rows from 8/13/2019 forward.  Form that table we pull the MIN ( [fortnight Start Date] ) which gives us 8/13/2019

• NextFortnightStart = 8/13/2019

VAR PrevFortnightStart =
    CALCULATE (
        MAX ( 'Table'[Fortnight Start Date] ),
        FILTER ( 'Table', 'Table'[Fortnight Start Date] < CurrentFortnightStart )
    )

This filters to table to just the rows where the [Fortnight Start Date] < 7/30/2019.  This gives us all the rows from 7/29/2019 back.  From that we pull the MAX (  [Fortnight Start Date]  ) which gives us 7/16/2019.

• PrevFortnightStart = 7/16/2019

VAR RowFortnightStart = 'Table'[Fortnight Start Date]

When working on a calculated column we are iterating over all the rows, this code gives us the [Fortnight Start Date] for the row we are currently iterating.  for example, if we are iterating the row with a Date of 7/14/2019 this variable will be 7/2/2019, the [Fortnight Start Date] of that row.

RETURN
    SWITCH (
        TRUE (),
        RowFortnightStart = CurrentFortnightStart, "Current Period",
        RowFortnightStart = NextFortnightStart, "Next Period",
        RowFortnightStart = PrevFortnightStart, "Previos Period"
    )

This piece is evaluated on every row as we iterate down the table.  SWITCH ( TRUE()...) goes through the conditions in the list and when it finds the first one that is TRUE returns the output.
So, on the row with a date of 7/21/2019 we have the following values:

• CurrentFortnightStart = 7/30/2019
• NextFortnightStart = 8/13/2019
• PrevFortnightStart = 7/16/2019
• RowFortnightStart = 7/16/2019 (this is the [Fornight Start] for the 7/21/2019 row).

Then it does it's check.

7/16/2019 = 7/30/2019 FALSE,

7/16/2019 = 8/13/2019 FALSE,

7/16/2019 = 7/16/2019 TRUE, "Previous Period"

[Anonymous]

Thank you so so much! This has been so helpful!