Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Pay Periods and Current Periods

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. ...
  • Stachu's avatar
    Stachu
    7 years ago

    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's avatar
    Stachu
    7 years ago

    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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
Stachu
Icon for Community Champion rankCommunity Champion
7 years ago

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's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you so much. That was so helpful!

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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!