topic Find correct date record for DAX formula in DAX Commands and Tips

Find correct date record for DAX formula

Anonymous — Sat, 11 Jun 2022 12:41:53 GMT

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:

HT - Target Hours = if( [Total Hours]<>0, ([HT - Days Worked] * 'Report Measures'[HT - # of Hours Per Day]) * [HT - % of Days])

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)

The problem is sometimes that changes and there is no set timeline when it changes the table below shows an example of how it shows up, is there a way to modify the above formula to check each month and use that # of hours per day for when there is a change? Some people could have no change since they started and some has 5 - 6 a year so is there a way to say find the time range in the start and end date below to get the $ of days worked?

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

Re: Find correct date record for DAX formula

v-yalanwu-msft — Tue, 14 Jun 2022 02:46:07 GMT

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.

Re: Find correct date record for DAX formula

Anonymous — Tue, 14 Jun 2022 03:01:20 GMT

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.