Forum Discussion

shareezsaleem's avatar
shareezsaleem
Icon for Helper III rankHelper III
4 years ago
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('Calenda...
Need Help
  • mwegener's avatar
    mwegener
    4 years ago

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

     

mwegener's avatar
mwegener
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

Hi shareezsaleem ,

 

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

Assuming the sorting of the column was set correctly.

 

shareezsaleem's avatar
shareezsaleem
Icon for Helper III rankHelper III
4 years ago

Not working.

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

 

  • mwegener's avatar
    mwegener
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    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's avatar
      shareezsaleem
      Icon for Helper III rankHelper III
      4 years ago

      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's avatar
        mwegener
        Icon for Most Valuable Professional rankMost Valuable Professional
        4 years ago

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