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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

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?

1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

2 REPLIES 2
mahoneypat
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


lbendlin
Super User
Super User

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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