Forum Discussion
Solved
Multiple months selected, consider the oldest
Hi Team,
I have below formula:
Count Of Contracts = SWITCH(TRUE(),VALUEs(Contracts_Details[Status_Revised])="Beginning",CALCULATE(DISTINCTCOUNT(Contracts_Details[Contract Ref.]),filter('Calendar','Calendar'[Month Name]="Jan")),VALUES(Contracts_Details[Status_Revised])="Expired" || Values(Contracts_Details[Status_Revised])="Expired On Last Day",-1*DISTINCTCOUNT(Contracts_Details[Contract Ref.]),DISTINCTCOUNT(Contracts_Details[Contract Ref.]))
I need to replace the Jan (highlighted) with below but it doesn't work as it filter all months selected.
Multiple Selection_Month = LEFT(CONCATENATEX(VALUES('Calendar'[Month Name]),'Calendar'[Month Name],","),3)
Basically, what I am looking is to count the contract numbers based on month & year selection. If I select multiple months, for e.g. Feb & Mar- I need to count only contracts created in Feb.
do you also have a numeric month column?
Then it would look like this.
CALCULATE(DISTINCTCOUNT(Contracts_Details[Contract Ref.]),filter('Calendar','Calendar'[Month No]=MIN('Calendar'[Month No])))
5 Replies
Hi shareezsaleem ,
I think MIN('Calendar'[Month Name]) should always return the oldest month.
Assuming the sorting of the column was set correctly.
Not working.
If I clear the month name (Slicer) also, it should work- consider JAN month's no.
Hi shareezsaleem ,
made a mistake in the behavior.
Maybe you can better use the month as a number to filter or determine the assignment as follows.
Oldest Month = MINX(FILTER(ALL('Calendar'[Month Name], 'Calendar'[Month No]), 'Calendar'[Month No] = MIN('Calendar'[Month No])), 'Calendar'[Month Name])
