Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
@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. |
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
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 |
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. |
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
23 | |
22 |