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))

Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

HI Greg_Davurse

 

I recommend you look at pivoting your table for Power BI.

 

I suggest the following format (which can be done in the Query Editor)

 

Employee No,  Date  , Payment
Bill       , Jan 7  , 112
Bill       , Jan 21 , 124 
Bill       , Feb 25 , 300
James      , Jan 21 , 123
...
...

Once you have the data in this format, you can create a relationship to a Calendar table on the Date column and you'll find the measures much easier to create.  We can help with that if need.

 

Phil

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

    Hi Phil_Seamark, 

     

    Thanks for the reply. The data is actually already in that format. The image of the Pivot Table you see is an example of the matrix I have in my PBI model. With that being said, I also have a Calendar table that has a relationship to the Employee Paid table (which contains the data you see in the matrix). Further thoughts? 

     

    Thanks, 

     

    Greg