topic Re: Allocating Number of Days out the their respective week periods in DAX Commands and Tips

Allocating Number of Days out the their respective week periods

Anonymous — Mon, 21 Jun 2021 14:43:53 GMT

Here's what I'm trying to work through.

I injury data where I have the occurance date and the number of days away from work.

i.e. Occurance Date: 1/1/2020, Days Away: 54

My goal is to allocate the number of days out their respective weeks.

I've not been successfull to this point and am open to suggestions.

Thanks,

Paul

Re: Allocating Number of Days out the their respective week periods

AlB — Mon, 21 Jun 2021 15:58:15 GMT

Hi Anonymous

Your request is a bit vague. It would help if you can show some sample input data and, especially, the exact output you want for it. Do you want the results in a table with all the weeks? How exactly? In a visual? How? other options?

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: Allocating Number of Days out the their respective week periods

Anonymous — Mon, 21 Jun 2021 16:34:29 GMT

Original Data

Case Number	Location	Occurance Date	Days Out of Work
1	100	1/1/2020	40
2	101	1/20/2020	25
3	100	2/2/2020	90
4	102	2/15/2020	20
5	101	2/15/2020	15

Output

Assumptions:

Fiscal Year Starts: 1/1/2020

Fiscal Week Starts: 1/1/2020

*I'm using a fiscal date table in my data model.

Week Number	Location	Days Away
1	100	7
1	101
1	102
2	100	7
2	101	1
2	102
3	100	7
3	101	7
3	102
4	100	14
4	101	7
4	102
5	100	7
5	101	7
5	102
6	100	7
6	101	8
6	102	6

Re: Allocating Number of Days out the their respective week periods

AlB — Mon, 21 Jun 2021 17:49:20 GMT

Anonymous

See the attached file for a possible solutions. I am not getting the same results as the output you show. You might have to tweak the DateT table to have the exact weekn numbers you need.

Days away = VAR auxT_ = ADDCOLUMNS ( SUMMARIZE ( Table1, Table1[Case Number], Table1[Occurance Date], Table1[Days Out of Work] ), "@res", VAR daysOut_ = DATESBETWEEN ( DateT[Date], [Occurance Date], [Occurance Date] + [Days Out of Work] - 1 ) VAR daysInWeek_ = DISTINCT ( DateT[Date] ) RETURN COUNTROWS ( INTERSECT ( daysOut_, daysInWeek_ ) ) ) RETURN SUMX ( auxT_, [@res] ) + 0

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: Allocating Number of Days out the their respective week periods

Anonymous — Mon, 21 Jun 2021 19:25:43 GMT

It's a great start, thank you.

I noticed where the data added up correctly, yet I'm seeing some discrepencies.

In this example, the location total is correct, yet when looking at fiscal month for within a fiscal year matrix the numbers don't add up.

When looking back previous years, the variance increases and it's always lower than the actual. I'd expect any the distributed value to be higher due to carry over.

I renamed the measure you provided as Dist Days Away