Forum Discussion

Greg_Davurse's avatar
Greg_Davurse
Icon for Helper I rankHelper I
9 years ago
Solved

Calculate No Payment Last 2 rolling weeks

Hi All,    I’m trying to calculate the number of weeks an employee hasn’t been paid for the last two weeks and I’m completely stuck. I need to return either ‘0’ indicating paid both weeks, ‘1’ indi...
Need Help
  • MarkS's avatar
    MarkS
    9 years ago

    There is probably a better formula for this but this should work

    Trial1 = IF(ISBLANK( CALCULATE(COUNTROWS(Table1),DATESBETWEEN(DateTable[date],TODAY()-14,TODAY())))

    ,2,

    MOD(CALCULATE(COUNTROWS(Table1),DATESBETWEEN(DateTable[date],TODAY()-14,TODAY())),2))

MarkS's avatar
MarkS
Icon for Resolver IV rankResolver IV
9 years ago

There is probably a better formula for this but this should work

Trial1 = IF(ISBLANK( CALCULATE(COUNTROWS(Table1),DATESBETWEEN(DateTable[date],TODAY()-14,TODAY())))

,2,

MOD(CALCULATE(COUNTROWS(Table1),DATESBETWEEN(DateTable[date],TODAY()-14,TODAY())),2))

  • Greg_Davurse's avatar
    Greg_Davurse
    Icon for Helper I rankHelper I
    9 years ago

    For some reason i don't think its working as i expected. I'm getting a '1' and '2' to show up when running the equation, but not a '0' when the employee has worked last two weeks.  Below shows what I get when running the calculation and generate a calculated column...doesn't seem right either.  The same holds true when running as a measure. 

     

    I'm going to keep performing trial and error to see what I can come up with, as long as I don't throw my pc through the window, lol.  Thanks for the help so far. 

    • MarkS's avatar
      MarkS
      Icon for Resolver IV rankResolver IV
      9 years ago

      Hi Greg,

      Sorry I think that I misinterpreted what you were looking for.

      If you just have the employee and the measure it will work, but not when dates are added.

      • Greg_Davurse's avatar
        Greg_Davurse
        Icon for Helper I rankHelper I
        9 years ago

        Mi Mark, 

         

        Thanks for the response. If I can create the table you have shown on your last post, that will serve as a temporary fix. I would just have two tables. One showing the actual payments and one showing the employee name with '2', '1' , or '0'. 

         

        Let me see what I come up with and I'll let you know. 

         

        Thanks, 

         

        Greg