topic Re: Compare dates in DAX Commands and Tips

Compare dates

Anonymous — Mon, 03 Aug 2020 13:53:47 GMT

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?

Re: Compare dates

lbendlin — Mon, 03 Aug 2020 23:44:39 GMT

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

Re: Compare dates

mahoneypat — Tue, 04 Aug 2020 00:02:46 GMT

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