Counting Escalation ID where Type is "Executive" and is in the last year

[Anonymous]

Hi all,

I have this in the DAX column but can't get it to work.

Test = CALCULATE(COUNT(Escalation[Id]),DATESINPERIOD(Escalation[Created Date],TODAY(),-1,YEAR), Escalation[Type] = "Support Manager")

Am I missing something? It's not filtering correctly for the Type

Getting error saying "A date column containing duplicate dates was specified in the call to function 'DATESINPERIOD'. This is not supported.

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

Based on my test, You should create Calendartable using the formula. Then create relationship between dimtime and Escalation based on date column.

dimtime = CALENDARAUTO()

Then your formula should work well. 

Test1 = CALCULATE(COUNT(Escalation[Id]),DATESINPERIOD(dimtime[Date],TODAY(),-1,YEAR), Escalation[Type] = "Support Manager")

For more details, please check the pbix as attched.

https://www.dropbox.com/s/bunja64n7j994q9/Counting%20Escalation%20ID.pbix?dl=0

Regards,

Frank

[v-frfei-msft]

Hi @Anonymous,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,

Frank