Forum Discussion
Helper ICalculate 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’ indicating paid one week, or ‘2’ indicating not paid last two weeks.
I think my problem is coming from the following: if an employee did not work, there is no record showing their pay = $0 for that date, and therefore no record. Any suggestions on how I can calculate this? See the below spreadsheet for an example matrix.
I tried to use COUNTBLANK, but because the record doesn't exist, it does show up as being a blank in the Matrix, but there are no blank values to calculate in the dataset.
The values in the below matrix represent a the data field [Paid] in the dataset.
Bill would return '1', James would return '0' and Susan would return '2'
Thanks in advance!
Greg
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))
8 Replies
MarkSResolver IV
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
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
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.
- Phil_Seamark
Microsoft Employee
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
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
- MarkS
Hi Greg,
Could you clarify.
You stated "I need to return either ‘0’ indicating paid both weeks, ‘1’ indicating paid one week, or ‘2’ indicating not paid last two weeks."
Then below in the example stated
"Bill would return '1', James would return '0' and Susan would return '2'"
which contradicts the first statement.
If you require 2 to indicate paid in the last two weeks and zero for not paid in the last two weeks CountRows might be the function you require.
- Greg_Davurse
Hi MarkS,
Sorry for the confusion. I had James and Susan mixed up. James would return '2', since he's not worked last two weeks, and Susan would return '0', since she has worked the last two weeks.
