Compare dates based on a selected date

[macwhe]

Hi All,

I need to add a measure to calculate Total amount based on a selected Date .  However I need to compare the selected date against Accounting Date and Freeze Date . The filter in the measure shoudl be Accountdate <= Selected Date and Freeze Date > Selected Date 

 Here is the table :

Before filter

Lets suppose the selected date is 31 March 2021.  so the expected result should be

Expected results

Thanks a lot

[macwhe]

Hello 

Unfortunately it didnt work .  It shows blank If I add this measure.  FYI, my dimension date  has a relationship with Freeze date. Any ideas?

Thanks a lot again

[selimovd]

Hey @macwhe ,

can post a screenshot of the data model, relationships and of the report page.

Otherwise if the data is not sensitive can you upload it or send it directly to me?

The example works for me, but dependent of your data model it might need a different approach for your model.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[macwhe · Accepted solution]

You were right . I have deleted the relationship and now it works fine

Thanks you much for your help

[selimovd]

Hey @macwhe ,

check if the following measure works for you:

Sum Amount =
VAR vSelectedDate =
    SELECTEDVALUE( DateTable[Date] )
RETURN
    CALCULATE(
        SUM( myTable[Amount] ),
        myTable[AccountingDate] <= vSelectedDate && myTable[Freeze Date] > vSelectedDate
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic