Forum Discussion

drinko's avatar
drinko
Helper I
5 years ago
Solved

restart calculated counter on max value

I have a column called Pay Period that I have calculated based on a date, but its not a regular calendar schedule so I can't use any date functions for this.  Basically I am counting pay periods for each year.  There are 26, but they periods end on dates outside their year so I can't use regular date fuctions.  The first year it works great...my pay periods go from 1 to 26, then in year 2 they go from 27 to 52 and so on.  I need them to reset after 26 and go back to 1 and then keep incrementing until they hit 26 again...and then reset to 1.  So basically period 27 and 53 need to be period 1.  This needs to be based off a max value and not a date function.  For example, Period 1 of 2021 is 12/18/2020 to 12/31/2020.  So I can't use regular date functions to my knowledge.  My dataset starts with 2019 pay periods (which starts 12/21/2018) and will go to infinity.  

 

ADP_Labor[Timecard Pay Date] row into a Pay Period.  There are 26 pay periods per year.   I just need help figuring out how to put period 27 as period 1 (first one in a new year).  Any ideas?

 

pay period = FLOOR(DATEDIFF(DATE(2018,12,21),ADP_Labor[Timecard Pay Date],DAY)/14,1)+1
Need Help
  • Jimmy801's avatar
    Jimmy801
    5 years ago

    Hello drinko 

     

    a simple solution would be to use nested ifs like this

    PayPeriod = if(
    (FLOOR(DATEDIFF(date(2018,12,21),[Date],DAY)/14,1)+1)<27, 
    (FLOOR(DATEDIFF(date(2018,12,21),[Date],DAY)/14,1)+1), 
    if(
        (FLOOR(DATEDIFF(date(2018,12,21),[Date],DAY)/14,1)+1)>26&&(FLOOR(DATEDIFF(date(2018,12,21),[Date],DAY)/14,1)+1)<53, 
        (FLOOR(DATEDIFF(date(2018,12,21),[Date],DAY)/14,1)+1)-26,
        blank()//etc.....
    )
)


    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

1 Reply

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    Hello drinko 

     

    a simple solution would be to use nested ifs like this

    PayPeriod = if(
    (FLOOR(DATEDIFF(date(2018,12,21),[Date],DAY)/14,1)+1)<27, 
    (FLOOR(DATEDIFF(date(2018,12,21),[Date],DAY)/14,1)+1), 
    if(
        (FLOOR(DATEDIFF(date(2018,12,21),[Date],DAY)/14,1)+1)>26&&(FLOOR(DATEDIFF(date(2018,12,21),[Date],DAY)/14,1)+1)<53, 
        (FLOOR(DATEDIFF(date(2018,12,21),[Date],DAY)/14,1)+1)-26,
        blank()//etc.....
    )
)


    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy