Forum Discussion
Converting a payroll data Measure into a Table Column
Hi there,
I am looking to find a way to speed up a measure that converts bi-weekly data into monthly. But the problem is that the measure takes a long time to calculate with the amount of data. I think that the best solution is to pre-calculate into table columns. So, I am looking to host the measure's end result as a table column (or two?) for a faster payroll report, and to be able to share the table with other reports.
I am working with payroll data, which comes on a bi-weekly basis. The measure below takes this bi-weekly data, and converts into monthly. In the measure, [Bi-Weekly Earnings] is my data field, and I have Start & End date fields that specify the dates covered by the payroll. I got this measure with help from this forum.
~Bi-Weekly Earnings by Month = CALCULATE( SUMX( SUMMARIZE( filter( CROSSJOIN('2022 Payroll Reports','Date'), 'Date'[Date] >= '2022 Payroll Reports'[2022 Payroll Calendar.Start Date] && 'Date'[Date] <= '2022 Payroll Reports'[2022 Payroll Calendar.End Date]),'2022 Payroll Reports'[Index],'Date'[Date], '2022 Payroll Reports'[[Bi-Weekly Earnings]]],'2022 Payroll Reports'[2022 Payroll Calendar.Start Date], '2022 Payroll Reports'[2022 Payroll Calendar.End Date]),
DIVIDE('2022 Payroll Reports'[[Bi-Weekly Earnings]]], DATEDIFF('2022 Payroll Reports'[2022 Payroll Calendar.Start Date], '2022 Payroll Reports'[2022 Payroll Calendar.End Date],day)+1) ) ) |
Visual of raw data:
The result of the measure:
How can I convert this measure into a table column? Since many payrolls overlap two months, do I need two or more columns to hold the amounts for each month?
2 Replies
sjhand , if you have start and end date of week, then refer if these can help
Between Dates - Dates between
Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785Tables way
https://amitchandak.mediumcom/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2amitchandakthis is great. I am looking at the tables method and hoping to use the GENERATE function.
I have start and end dates in the data and should be able to use GENERATE to replicate each row 14 times as each payroll covers 14 days.
Now how can I distribute some of the payroll cost fields into each of the replicated rows? Can I simply nest my current measure into the GENERATE function?
For example, I used GENERATE for one of the payrolls that covers 7 days in the example below and it created 7 rows, which makes sense. How can I split [Bi-Weekly Earnings], [Total Hours], etc. 7 ways into each one of the dates?
