Multiple months selected, consider the oldest

[shareezsaleem]

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.

[mwegener]

Hi @shareezsaleem ,

I think MIN('Calendar'[Month Name]) should always return the oldest month.

Assuming the sorting of the column was set correctly.

[shareezsaleem]

Not working.

If I clear the month name (Slicer) also, it should work- consider JAN month's no.

[mwegener]

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])

[shareezsaleem]

I am not able to attach files in this thread.

Can you please explain me how to ammend this formula?

CALCULATE(DISTINCTCOUNT(Contracts_Details[Contract Ref.]),filter('Calendar','Calendar'[Month Name]="Jan"))

Here Jan should be replaced by the oldest month, if no month is selected it should consider 'Jan'.

If I select May, Feb, July- then it should consider Feb (Oldest month).

Keep in mind that I have 2 Slicers- One for year and another for Month Name.

[mwegener · Accepted solution]

@shareezsaleem 

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])))