Can't make IF work properly when trying to set a default date v a date selected in a slicer..

[DougHoog]

I want to drive calculations in DAX based on EITHER the latest transaction date or a date selected by the user in the report with a date slicer. The date table is imported from a data warehouse and is not calculated. I'm not proficient in M and find it more difficult than DAX. 

DAX for the latest transaction date works fine:
LastTransDate =
VAR __LastTransactionDate = DATEVALUE(MAXX ( {MAX ( '_Invoices'[Invoice Date] )}, [Value] ))
RETURN __LastTransactionDate

I have then created a slicer on the report that shows the last 90 days (relative). If the user opens the report and has not selected a date, the calculations would work based on the LastTransDate. But if they want to OVERRIDE the date being used in the MTD, YTD calculations, then they would click a date in the slicer...

So I wrote: 

SelectedDate =  IF(HASONEVALUE ('Date'[Date]) , 'Date'[Date], 'Date'[LastTransDate])

Unfortunately, this always shows the FALSE value of the LastTransDate. I don't know why because when I make a measure of HASONEVALUE ('Date'[Date]) and display that on the report, and then select a single date, the measure shows TRUE, which would lead one to believe the date selected would be the value for SelectedDate. Picture attached.

[DougHoog]

I have tried other things like ISBLANK but that hasn't worked either.

[DougHoog]

In the picture, scenario 1 has no date selected and you can see in the green SelectedDate shows the date of last data. 🙂 But scenario 2 does have a date selected in the slicer (July 26, 2024) and the FilteredDateCount shows True, so one would expect the SelectedDate to be 2024-07-26.

[Anonymous · Accepted solution]

Hi @DougHoog  , 

I tried to create a sample data myself based on your requirement and implemented the result. Please check if there is anything that can be improved. Here is the steps:

1.Create simple data:

 

2.Based on yours, I created two new meausres:

Filtereddate = IF(ISFILTERED('Date'[Date]),MAX('Date'[Date]),'_Invoices'[LastTransDate]) 

Isfiltered = ISFILTERED('Date'[Date]) 

3.The final result is as follows:

 

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[DougHoog]

Thank you. I will test this tonight.

[Anonymous]

Hi  @DougHoog , 

May I ask if you conduct the test and get the expected results? If so, can you mark the correct answer as solution to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!