sum by rolling 12 months period

[Dan_Wis]

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

[v-yangliu-msft]

Don't @Dan_Wis,

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.

[amitchandak]

@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

[Dan_Wis]

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?