cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Maikeru Regular Visitor
Regular Visitor

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.

screenshot2.png

 

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Calculating Min/Max date columns based on selected values

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Calculating Min/Max date columns based on selected values

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Highlighted
CheenuSing Super Contributor
Super Contributor

Re: Calculating Min/Max date columns based on selected values

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Maikeru Regular Visitor
Regular Visitor

Re: Calculating Min/Max date columns based on selected values

Thank you @Ashish_Mathur!
That's exactly what I was looking for!

Super User
Super User

Re: Calculating Min/Max date columns based on selected values

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 6 members 2,610 guests
Please welcome our newest community members: