Forum Discussion

macwhe's avatar
macwhe
Helper I
5 years ago
Solved

Compare dates based on a selected date

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

Need Help
  • macwhe's avatar
    macwhe
    5 years ago

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

    Thanks you much for your help

     

4 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    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
     
  • macwhe's avatar
    macwhe
    Helper I
    5 years ago

    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's avatar
      selimovd
      Most Valuable Professional
      5 years ago

      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
       
      • macwhe's avatar
        macwhe
        Helper I
        5 years ago

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

        Thanks you much for your help