Rolling FTEs till current month

[Switto]

Hi everyone,

We are showing a trend graph for the rolling FTEs for pipeline and completed projects for the whole FY(July-June).

The pipeline FTE measure is working fine. For the closed FTEs, we need rolling FTE till the current month, and the rest of future months should show blank.

As you can see the measure is showing the data till Dec'22 but we need it till Dec'21(Current month).

We have used below formula to calculate the measure but it is not working.

mRolling_Closed_FTE = CALCULATE([mCompleted_FTE],FILTER(ALL('Calendar'),'Calendar'[Date]<= MAX('Calendar'[Date])),MONTH('Calendar'[Date])<= MONTH(TODAY()))

Request your help in solving the issue.

Thanks

S

[amitchandak]

@Switto , Try with EOMonth , not month

mRolling_Closed_FTE = CALCULATE([mCompleted_FTE],FILTER(ALL('Calendar'),'Calendar'[Date]<= MAX('Calendar'[Date]) && eomonth('Calendar'[Date],0)<= eomonth(TODAY(),0)))

[Switto]

Thanks for coming back with the suggestion however after trying the above formula, it is showing the same result as before. 

[amitchandak · Accepted solution]

@Switto ,

mRolling_Closed_FTE =
var _max= eomonth(TODAY(),0)
return
CALCULATE([mCompleted_FTE],FILTER(ALL('Calendar'),'Calendar'[Date]<= MAX('Calendar'[Date]) && eomonth('Calendar'[Date],0)<= _max))

or

mRolling_Closed_FTE =
var _max= eomonth(TODAY(),0)
return
if( eomonth(max('Calendar'[Date]),0)<= _max CALCULATE([mCompleted_FTE],FILTER(ALL('Calendar'),'Calendar'[Date]<= MAX('Calendar'[Date]) )), blank())

[Switto]

Hi Amit, Below formula worked 🙂

mRolling_Closed_FTE =

var _max= eomonth(TODAY(),0)

return

if( eomonth(max('Calendar'[Date]),0)<= _max ,CALCULATE([mCompleted_FTE],FILTER(ALL('Calendar'),'Calendar'[Date]<= MAX('Calendar'[Date]) )), blank())