Pay Periods and Current Periods

[Anonymous]

Hi All,

I have data on a daily granularity and i need to create a pay period column that groups these daily dates into ranges. For example, i need to return pay period ending date for each date. 

1st pay period ends on 15th of month and second ends on the end of the month. 

Once i have this, i need to be able to dynamically assign a tag saying whether the pay period ending contains current date which is TODAY() and if it does, the entire pay period is it's tagged with a "CURRENT PERIOD" tag (a custom column), if not, it's tagged with "PREVIOUS PERIOD" or "FUTURE PERIOD" depending on whether the other pay periods exist ahead of behind the current period. 

How do i achieve this? 

Thanks all. 

[Stachu · Accepted solution]

this should do

PayPeriod = 
-- variable declaration section
VAR __CurrentDate = 'Calendar'[Date]    -- returns the date for current row (because it's calculated column)
-- output section
RETURN
IF(
    DAY(__CurrentDate) <= 15,    -- checks if the day of current row date is <= 15
    __CurrentDate - DAY(__CurrentDate) + 15 ,    -- if it is, it takes the date subtracts the number of days of this date (which gives 0) and adds 15
    EOMONTH(__CurrentDate, 0)    -- if it's not it gives the last day of the month
    )

Period =
-- variable declaration section
VAR __CurrentPayPeriod = 'Calendar'[PayPeriod]    -- returns the pay period for a current row
VAR __Today = TODAY ()    -- returns the today's date
VAR __TodayPayPeriod =    -- returns the pay period for today (using method from [PayPeriod]
    IF (
        DAY ( __Today ) <= 15,
        TODAY () + 15 - DAY ( TODAY () ),
        EOMONTH ( TODAY (), 0 )
    )
VAR __PreviousPayPeriod =    -- calculates the maximum date in PayPeriod column that is strictly before today's pay period
    CALCULATE (
        MAX ( 'Calendar'[PayPeriod] ),
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[PayPeriod] < __TodayPayPeriod )
    )
VAR __FuturePayPeriod =    -- calculates the minimum date in PayPeriod column that is strictly after today's pay period
    CALCULATE (
        MIN ( 'Calendar'[PayPeriod] ),
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[PayPeriod] > __TodayPayPeriod )
    )
-- output section
RETURN
    IF (
        __TodayPayPeriod = __CurrentPayPeriod,
        "Current Period",
        IF (
            __CurrentPayPeriod = __PreviousPayPeriod,
            "Previous Period",
            IF ( __CurrentPayPeriod = __FuturePayPeriod, "Future Period", "Other" )
        )
    )

[Stachu]

you may need to change the 'Calendar'[Date] to whatever would be your date column

PayPeriod = 
VAR __CurrentDate = 'Calendar'[Date]
RETURN
IF(
    DAY(__CurrentDate) <= 15, 
    __CurrentDate - DAY(__CurrentDate) + 15 , 
    EOMONTH(__CurrentDate, 0)
    )

This column uses the previous one, so it should be added later

Period = 
VAR __CurrentPayPeriod = 'Calendar'[PayPeriod]
VAR __Today = TODAY()
VAR __TodayPayPeriod = IF(DAY(__Today) <= 15, TODAY() - DAY(TODAY()) + 15, EOMONTH(TODAY(), 0))
RETURN
IF(__TodayPayPeriod=__CurrentPayPeriod, "Current Period",
    IF(__CurrentPayPeriod < __TodayPayPeriod, "Previous Period", "Future Period"))

[Anonymous]

Hi @Stachu ,

Thank you so much! This was exactly what i was after! 

I did have one last question though. Basically, i want to have a slicer that shows "current period", "past period" and "future period". When i select current period, it shouldof course show me that current pay period sales and given that there can only be one 'current period' at a time in the date table, it will always show output for one pay period. 

But for the "past period" and "future period" tags, i want it to always just show me 1 past period from the current period and 1 future period from the current period, not an aggregation of sales across all past period and future periods. 

How do i make it so that based on whatever the current period is, it's only showing me one period prior to current period and one period future of curent period and doing it dynamically as current period will change dynamically. 

Thanks so much!

[Stachu · Accepted solution]

this should do

PayPeriod = 
-- variable declaration section
VAR __CurrentDate = 'Calendar'[Date]    -- returns the date for current row (because it's calculated column)
-- output section
RETURN
IF(
    DAY(__CurrentDate) <= 15,    -- checks if the day of current row date is <= 15
    __CurrentDate - DAY(__CurrentDate) + 15 ,    -- if it is, it takes the date subtracts the number of days of this date (which gives 0) and adds 15
    EOMONTH(__CurrentDate, 0)    -- if it's not it gives the last day of the month
    )

Period =
-- variable declaration section
VAR __CurrentPayPeriod = 'Calendar'[PayPeriod]    -- returns the pay period for a current row
VAR __Today = TODAY ()    -- returns the today's date
VAR __TodayPayPeriod =    -- returns the pay period for today (using method from [PayPeriod]
    IF (
        DAY ( __Today ) <= 15,
        TODAY () + 15 - DAY ( TODAY () ),
        EOMONTH ( TODAY (), 0 )
    )
VAR __PreviousPayPeriod =    -- calculates the maximum date in PayPeriod column that is strictly before today's pay period
    CALCULATE (
        MAX ( 'Calendar'[PayPeriod] ),
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[PayPeriod] < __TodayPayPeriod )
    )
VAR __FuturePayPeriod =    -- calculates the minimum date in PayPeriod column that is strictly after today's pay period
    CALCULATE (
        MIN ( 'Calendar'[PayPeriod] ),
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[PayPeriod] > __TodayPayPeriod )
    )
-- output section
RETURN
    IF (
        __TodayPayPeriod = __CurrentPayPeriod,
        "Current Period",
        IF (
            __CurrentPayPeriod = __PreviousPayPeriod,
            "Previous Period",
            IF ( __CurrentPayPeriod = __FuturePayPeriod, "Future Period", "Other" )
        )
    )

[Stachu · Accepted solution]

I've added the comments in the accepted solution, lat me know if something is not clear. I use variables, they are explained in more detail here
https://www.sqlbi.com/articles/variables-in-dax/

also you can see what is calculated in each step by changing, what's returned, e.g. code below will return the today pay period, you can only return one thing at a time, I commented the actual answer in the code below)

Period =
-- variable declaration section
VAR __CurrentPayPeriod = 'Calendar'[PayPeriod] -- returns the pay period for a current row
VAR __Today =
    TODAY () -- returns the today's date
VAR __TodayPayPeriod =
    -- returns the pay period for today (using method from [PayPeriod]
    IF (
        DAY ( __Today ) <= 15,
        TODAY () + 15
            - DAY ( TODAY () ),
        EOMONTH ( TODAY (), 0 )
    )
VAR __PreviousPayPeriod =
    -- calculates the maximum date in PayPeriod column that is strictly before today's pay period
    CALCULATE (
        MAX ( 'Calendar'[PayPeriod] ),
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[PayPeriod] < __TodayPayPeriod )
    )
VAR __FuturePayPeriod =
    -- calculates the minimum date in PayPeriod column that is strictly after today's pay period
    CALCULATE (
        MIN ( 'Calendar'[PayPeriod] ),
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[PayPeriod] > __TodayPayPeriod )
    ) -- output section
RETURN
    __TodayPayPeriod
/*
    IF (
        __TodayPayPeriod = __CurrentPayPeriod,
        "Current Period",
        IF (
            __CurrentPayPeriod = __PreviousPayPeriod,
            "Previous Period",
            IF ( __CurrentPayPeriod = __FuturePayPeriod, "Future Period", "Other" )
        )
    )
*/

[Anonymous]

Thank you so much. That was so helpful!

[Anonymous]

Hi @Stachu ,

THIS WORKED PERFECTLY! Thank you!!

I'm still new to Power BI. Could you explain the general logic of what you did? Thanks so much!