Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I have a problem with calculating number od days spent abroad by particular employee within 12m rolling period.
this is what I have:
Table 1
Employee ID | To Country | #Days | From Country | EOM_TravelDate | Key_2 |
6154 | SWITZERLAND | 5 | ITALY | 30/11/2019 | 6154-ITALY-SWITZERLAND |
6154 | SWITZERLAND | 4 | ITALY | 31/12/2019 | 6154-ITALY-SWITZERLAND |
6154 | NORWAY | 4 | ITALY | 29/02/2020 | 6154-ITALY-NORWAY |
6154 | SPAIN | 4 | ITALY | 31/12/2020 | 6154-ITALY-SPAIN |
6154 | SWITZERLAND | 7 | ITALY | 31/01/2020 | 6154-ITALY-SWITZERLAND |
I want to show in Table 2 number of days spent in particular country within 12m rolling period:
Table 2 = DISTINCT('Table 1'[Key_2])
Key_2 | #Days-12m back |
6154-ITALY-SWITZERLAND | |
6154-ITALY-NORWAY | |
6154-ITALY-SPAIN |
#Days-12m back = [Rolling 12 months_Measure]
Rolling 12 months_Measure =
var CurrentDate = MAX('Table 1'[EOM_TravelDate])
var PreviousDate = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-12,DAY(CurrentDate))
var Result =
CALCULATE(
SUM('Table 1'[#Days]),
FILTER(
'Table 1',
'Table 1'[EOM_TravelDate] > PreviousDate && 'Table 1'[EOM_TravelDate] <= CurrentDate
)
)
return
Result
Based on that criteria the result it gives me is 16 for 6154-ITALY-SWITZERLAND in Table 2, whereas it should be 7
Any ideas what is wrong?
Your support would be much appreciated!
thanks,
Best regards
Here are the steps you can follow:
1. Create a calculated column.
Rolling 12 months_measure =
var _CurrentDate =CALCULATE(MAX('Table'[EOM_TravelDate]),FILTER('Table','Table'[Key_2]=MAX('Table 2'[Key_2])))
return
CALCULATE(MAX('Table'[#Days]),FILTER('Table','Table'[EOM_TravelDate]=_CurrentDate))
2. Result:
You can download the PBIX file from here.
Best regards
Liu Yang
If this post helps,then consider Accepting it as the solution to help other members find it faster.
@Dan_Wis , add the key in filter
Rolling 12 months_Measure =
var CurrentDate = MAX('Table 1'[EOM_TravelDate])
var PreviousDate = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-12,DAY(CurrentDate))
var Result =
CALCULATE(
SUM('Table 1'[#Days]),
FILTER(
'Table 1',
'Table 1'[EOM_TravelDate] > PreviousDate && 'Table 1'[EOM_TravelDate] <= CurrentDate
&& 'Table 1'[Key_2] = 'Table 2'[Key_2]
)
)
return
Result
Hi @amitchandak ,
Thanks for support!
Adding the key in filter does not work as I got still 16 instead of 7.
It does work when I input manually PreviousDate: DATE(2019,12,31), however it doesn't make it dynamic for the future reports..
Any other suggestion?
User | Count |
---|---|
98 | |
91 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |