Forum Discussion
Find correct date record for DAX formula
Hello,
I am not sure how to do this in Power BI and was hoping for some help.
I have a formula that I use:
Its pretty basic it calculates the targeted number of hours someone should work per month by # of Hours Per Day (calculated using the number of days in the date table) * % of Days worked (their FTE # of Days worked /5)
| Provider ID | Provider Name | Provider Start Date | # of Days Worked Per Week | % of Days Worked | Monthly Case Target | Period Case Target | Yearly Case Target | provider_target_id | Start Date | End Date |
| 455104 | Jane Doe | 3-Aug-20 | 3 | 0.6 | 25 | 100 | 300 | 3732 | 16-May-22 | NULL |
| 455104 | Jane Doe | 3-Aug-20 | 5 | 1 | 41.67 | 166.67 | 500 | 3731 | 17-Oct-21 | 15-May-22 |
Here is my desired result:
| # of Days Worked | % of Days Worked | # of Hours Per Day | # of Days Worked | Desired Result | |
| Jan | 3 | 0.6 | 3.3871 | 31 | 2.0323 |
| Feb | 3 | 0.6 | 3.75 | 28 | 2.2500 |
| Mar | 3 | 0.6 | 3.3871 | 31 | 2.0323 |
| Apr | 3 | 0.6 | 3.5 | 30 | 2.1000 |
| May | Combination | 2.6878 | |||
| 1 - 16 | 3 | 0.6 | 3.3871 | 16 | 1.0489 |
| 19 - 31 | 5 | 1 | 3.3871 | 15 | 1.6389 |
| June MTD | 5 | 1 | 3.5 | 11 | 3.5000 |
2 Replies
v-yalanwu-msftCommunity Support
Hi, Anonymous ;
Sorry, I am confused. What is the calculation logic of the 2 columns marked in red?
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Yalan,
Worked # of Hours is the target # of hours (105) divided by number of days in month so for Jan 105/31 = 3.3871. And Desired Result is % of Days worked * # of days in month so far/ total number of days in month * Worked # of Hours Per Day ie for Jan = .6 * 31/31 * 3.3871 = 2.323. The main thing is how do I get the formula to grab the correct % of Days Worked based on the above first table so 1 Jan - 16 May .6 then for May 17 - Current Day 1 that is based on the start and end dates in the first able.