Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to Solution.
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))
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))
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.
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.
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
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.
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.
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
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
82 | |
70 | |
49 |
User | Count |
---|---|
143 | |
123 | |
107 | |
61 | |
55 |