Dax Needed for Rate for Zero Work week rolling

[Learner27]

Hi everyone,

I'm trying to create a measure for the "rate for $ work" on a weekly basis. For the first week, the calculation is as follows: I sum up all the measures and divide by the total hours of the task.

The formula for the
"rate for $ work" =R_service_earnings + C_Service_earnings + R_Act_Hours + C_Act_Hours + Extra_Working_time + Divisional_pay + Geo_pay + R_Install + C_Install + Indirect_Pay / Total_Hours_of_Task.

We need to use the above formula for the first week. After the first week, for the subsequent weeks, we need to multiply the "rate for $ work" from the previous week.

For example, in week 2, the formula for the
"rate for $ work" = R_service_earnings + C_Service_earnings + R_Act_Hours * "rate for $ work" (of Week 1) + C_Act_Hours * "rate for $ work" (of Week 1) + Extra_Working_time * "rate for $ work" (of Week 1) + Divisional_pay * "rate for $ work" (of Week 1) + Geo_pay * "rate for $ work" (of Week 1) + R_Install + C_Install + Indirect_Pay / Total_Hours_of_Task.

We should continue this pattern for the subsequent weeks as well.
For week 3, we would replace the multiplication with the "rate for $ work" of Week 2,
for week 4 with the "rate for $ work" of Week 3, and so on.

This process should continue for the upcoming weeks

Here Im providing sample data 

It should not stop by the year; my data starts from the year 2022. In the year 2022, only for week 1, we use our very first formula which I stated above. However, in the year 2023, we will have week 1 again. In this week, we need to use the rate for $ work of week 52 from 2022.

Could you please help me with this? Your assistance would be greatly appreciated

Week	R_service_earnings	C_Service_earnings	R_Act_Hours	C_Act_Hours	Extra_Working_time	Divisional_pay	Geo_pay	R_Install	C_Install	Indirect_Pay	Total_Hours_of_Task	rate for $ work
1	836	3874	7.49	2.89	0.67	2	5	69.8	24.8	2541	1552
2	736	2584	8.24	3.45	0.25	34	32	25	45	14	1452
3	875	475	4.86	7.85	0.45	25	45	14.35	13.45	2542	5284
4	8547	755	2.35	9.65	1.52	45	27	41.36	25.5	4452	2542

[some_bih]

Hi @Learner27 given all many to many relationship, are you sure this model is not ambigious? I mean you can reconcile some results and you are fine with it?

Nevertheless, you wrote sum up all measures like "The formula for the
"rate for $ work" =R_service_earnings + C_Service_earnings + R_Act_Hours + C_Act_Hours + Extra_Working_time + Divisional_pay + Geo_pay + R_Install + C_Install + Indirect_Pay / Total_Hours_of_Task."

In your measure folder there are some measures, but I could not find something like for R_service_earnings, or C_Service_earnings. Maybe I am missing something in overall model.

[Learner27]

Hi @tamerj1 , @lbendlin 

Please reply me ASAP it is very urgency 
I have attached more details for your understanding please let me know if further any 
your help will be apperciated

week_1

 Week 2

 

[lbendlin]

You may want to rename the sample file. The $ sign prevents the download.

[Learner27]

Hi @lbendlin 
If you are facing issues downloading this file, you can rename it. Thank you for your prompt reply; this task is very urgent for me. I really appreciate your assistance.

[lbendlin]

Please note that In this forum we have users helping users on a voluntary basis with no commitment.  If you need urgent support I suggest you engage a Microsoft partner.

[Learner27]

Hi @tamerj1 

Here is the sample PBIX file I have attached ,please help me it is very very urgency.
Sample Data For Rate $Zero Work.pbix