Converting a payroll data Measure into a Table Column

[sjhand]

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?

[amitchandak]

@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-o...

Tables 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-9ad6a84cf5f2

[sjhand]

@amitchandakthis 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?