Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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()
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |