Forum Discussion
Compare dates
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?
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
2 Replies
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()
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
