Compare dates

[Anonymous]

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?

[mahoneypat · Accepted solution]

If you use a date column from the same table in your slicer, it filters that table and prevents your desired functionality.  You can make a simple DAX table to be used in your slicer instead, then store the selectedvalue in a variable for comparison in the rest of your expression.

SlicerDates = DISTINCT('NPLM Import'[Plan R@R ...])

Ø Annual Turnover (EUR) Done = 
var thisdate = SELECTEDVALUE(SlicerDates[Plan R@R ...])
Return 
IF(
   MAX('NPLM Import'[Plan R@R (internal) Date]) >= thisdate, 
      IFERROR(SUM('NPLM Import'[Yearly average turnover (EUR)]), "")
)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[lbendlin]

What happens when 

Dates[DayOfMonth]

is 31 ? 🙂

You may want to use variables to debug you measure and see where it breaks.

By the way, generally you can just say max(date)+1 or you can use DATEADD()