Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

Compare dates not working

Hi there,

 

my aim is to compare two dates within an if clause.

Naturally, the formula should show all values, if their date is larger than my selected date (03.08.2020).

But I don't know why the following dax is not working

Ø Annual Turnover (EUR) Done = 
IF(
   MAX('NPLM Import'[Plan R@R (internal) Date]) >= (DATE(MAX(Dates[Year]), MAX(Dates[MonthOfYear]), 1+MAX(Dates[DayOfMonth]))), 
      IFERROR(SUM('NPLM Import'[Yearly average turnover (EUR)]), "")
)

 

Replacing the part after >= with MAX('Dates'[Date]) oder SELECTEDVALUE('Dates'[Date]) is either giving me no values or shows all values.

It works fine when I replace it with a date(2020,08,03) or today(), but I need to have it dynamically with a date slicer.

Ø Annual Turnover (EUR) Done = 
IF(
   MAX('NPLM Import'[Plan R@R (internal) Date]) >= (DATE(2020,08,03)), 
      IFERROR(SUM('NPLM Import'[Yearly average turnover (EUR)]), "")
)

 

Is there a way to avoid this bug?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Solved the problem myself.

The problem was that several dates had to be evaluated while only one was expected. I changed the "Dates" part with a new column which only shows the first date of each week for each date.

 

Ø Annual Turnover (EUR) Done = 
IF(
   MAX('NPLM Import'[Plan R@R (internal) Date]) >= SELECTEDVALUE('Dates(2)'[StartofWeek]), 
      IFERROR(SUM('NPLM Import'[Yearly average turnover (EUR)]), "")
)

 

 

Btw: There is nothing wrong with my dataset and connections are still maintained as before. My original post (https://community.powerbi.com/t5/DAX-Commands-and-Tips/Compare-dates/m-p/1268717) which was marked as spam, disappeared and suddenly returned, did help me solve the issue instead of instructing me.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Solved the problem myself.

The problem was that several dates had to be evaluated while only one was expected. I changed the "Dates" part with a new column which only shows the first date of each week for each date.

 

Ø Annual Turnover (EUR) Done = 
IF(
   MAX('NPLM Import'[Plan R@R (internal) Date]) >= SELECTEDVALUE('Dates(2)'[StartofWeek]), 
      IFERROR(SUM('NPLM Import'[Yearly average turnover (EUR)]), "")
)

 

 

Btw: There is nothing wrong with my dataset and connections are still maintained as before. My original post (https://community.powerbi.com/t5/DAX-Commands-and-Tips/Compare-dates/m-p/1268717) which was marked as spam, disappeared and suddenly returned, did help me solve the issue instead of instructing me.

Anonymous
Not applicable

There are things which are wrong about this formula. And certainly there is no bug in DAX itself. How many time do I read/hear people saying "It must be a bug" only to show them that it's not a bug but their misunderstanding of DAX? Same story here 🙂

 

First, if your [Plan R@R (internal) Date] is connected to Dates, then no wonder you don't get anything as a result. If you think carefully about what the below does:

MAX('NPLM Import'[Plan R@R (internal) Date])
>= 
DATE(
    MAX(Dates[Year]),
    MAX(Dates[MonthOfYear]),
    1+MAX(Dates[DayOfMonth])
)

you'll find out immediately that the first MAX can't be greater than the RHS of the inequality.

 

Secondly, what's the purpose of ISERROR? Do you really expect to get an error from

SUM('NPLM Import'[Yearly average turnover (EUR)])?

If you do, then it means there's something seriously wrong with your data model

Greg_Deckler
Super User
Super User

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.