Forum Discussion

Maikeru's avatar
Maikeru
Helper II
7 years ago
Solved

Calculating Min/Max date columns based on selected values

Hi,

 

I am trying to insert 2 new columns into my dataset containing the MIN/MAX values of the date slicer.

 

I was thinking about the following DAX, but it throws me a circular dependency error.

Min selected date (ALLSELECTED) = 
CALCULATE( MIN('Calendar'[Date]), ALLSELECTED('Calendar'[Date]) )

Appreciate if anyone would have an idea.

 

Here is the pbix file:
https://drive.google.com/file/d/1HRjJxByXmLVrgoH9IDnOAlEKHbGytiQo/view?usp=sharing

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

    You have to write measures, not calculated column formulas.  Delete all column from the Calendar Table except the Date column.  Write these measures

     

    Minimum date = CALCULATE(MIN('Calendar'[Date]),ALLSELECTED('Calendar'[Date]))

    Maximum date = CALCULATE(MAX('Calendar'[Date]),ALLSELECTED('Calendar'[Date]))

     

    Hope this helps.

9 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    You have to write measures, not calculated column formulas.  Delete all column from the Calendar Table except the Date column.  Write these measures

     

    Minimum date = CALCULATE(MIN('Calendar'[Date]),ALLSELECTED('Calendar'[Date]))

    Maximum date = CALCULATE(MAX('Calendar'[Date]),ALLSELECTED('Calendar'[Date]))

     

    Hope this helps.

    • kcpelesa1's avatar
      kcpelesa1
      New Member
      5 years ago

      Hi Ashish_Mathur , is it possible have this as a column?

       

      im saying this because im using a Matrix table so it doesnt allow measures in the rows.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        5 years ago

        Hi,

        Switch to a matrix visual and in the formatting pane, under the Values group, turn on Show on rows.

    • KShro's avatar
      KShro
      New Member
      2 years ago

      Hi,

      What are the min and max date codes doing and when to use it?

      Appreciate your explanation.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Maikeru

     

     

    Try the following as columns

     

     

    MinDate = CALCULATE( MIN('01-MasterCalendar'[Date]),
                           FILTER(ALL('01-MasterCalendar'[Date]),'01-MasterCalendar'[Year] =YEAR('01-MasterCalendar'[Date]) ))

     

    MaxDate = CALCULATE( Max('01-MasterCalendar'[Date]),
                           FILTER(ALL('01-MasterCalendar'[Date]),'01-MasterCalendar'[Year] =YEAR('01-MasterCalendar'[Date]) ))

     

    Replace '01-MasterCalendar' with your calendar table name.

     

    Using these expressions it will generate MIn and Max dates for each year in the calendar table.

     

     

    If this works for you please accept this as solution and also give KUDOS.

     

    Cheers

     

    CheenuSing