Remove Future Dates from Rolling 12 Month Measures
Hi all, Hoping someone can help and that I am not duplicating a previous post. I am working on revising many measures in my model to account for a future dated date table. I am currently stuck on Rolling 12 Month Hiring Sum. Here is the DAX: Rolling 12 Month External Hiring Sum = CALCULATE ( COUNTROWS ( 'External Hires' ), CALCULATETABLE ( DATESBETWEEN ( 'Date'[Date], NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Date'[Date] ) ) ), LASTDATE ( 'Date'[Date] ) ), 'Date'[DatesWithHires] = TRUE )) I created a calculated column on the Date table called DatesWithHires. This flags dates that are on or before the max hire date which would never be in the future. When I try to apply this using CALCULATETABLE there is no change. If I filter the visual itself by DatesWithHires = TRUE, then the visual works as expected. Here is a screenshot of the outcome from my measure: Here is a screenshot of what I am expecting. I can acheive this using a visual filter with DatesWithHires = True. I really need this in the measure though. Any guidance is much appreciated. Thanks in advance!
