cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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'

Greg

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

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

Helper I

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.

Resolver IV

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.

Helper I

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

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

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.

Helper I

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.

Microsoft Employee

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  , PaymentBill       , Jan 7  , 112Bill       , Jan 21 , 124 Bill       , Feb 25 , 300James      , 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

Proud to be a Datanaut!

Helper I

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors