Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
Greg_Davurse
Helper I
Helper I

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’ 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'

 

Capture.PNG

Thanks in advance!

 

Greg

1 ACCEPTED SOLUTION
MarkS
Resolver IV
Resolver 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))

View solution in original post

8 REPLIES 8
MarkS
Resolver IV
Resolver 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))

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. 

Capture2.PNG

 

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.

viz 03072017.PNG

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

MarkS
Resolver IV
Resolver IV

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'"

 

Capture.PNG

 

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. 

Phil_Seamark
Microsoft Employee
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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.