Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Filter on same day last year

Hello,   Just all the other posts I saw before, I also have been searching the internet/Microsoft forum for a couple hours and couldn't find a case that helped me find a solution.   I am in a car...
Need Help
  • Stachu's avatar
    Stachu
    7 years ago

    try this measure

    Res Nr Today-1YR = 
VAR __CurrentDate = TODAY()
VAR __LastYearDate = EDATE(__CurrentDate,-12)
RETURN
CALCULATE([Res Nr], SAMEPERIODLASTYEAR('Date Exp Return'[Date]), RezReporting[Date Booked 2]<__LastYearDate)

    For Jan it gives 3973 which is different than your calculation but it matches nr of rows in RezReporting, booked before 28-01-2018 (based on RezReporting[Date Booked2]), for Jan 2018 (based on RezReporting[Date Exp Return 2])

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Lin,

 

No, my date table is not until today().  I have attached a pbix file with explanation and an example of the desired result.

So basically I need a filter that tells me the situation exactly from the perspective 1 year ago...

 

 https://1drv.ms/u/s!AtxAI0GSGpOzgvxU-NhZ40pp51qceQ

 

I hope we can find a solution.

 

Thanks!

Merel

 

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
7 years ago

Hi,

 

With Today's date being 28 January 2019, this measure gives the correct answer.

 

=CALCULATE([Res Nr],DATESBETWEEN('Date Exp Return'[Date],EDATE(MIN('Date Exp Return'[Date]),-12),DATE(YEAR(MIN('Date Exp Return'[Date]))-1,MONTH(MIN('Date Exp Return'[Date])),DAY(TODAY()))))

Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Ashish,

     

    Thank you for your reply. It does not give me the result I want unfortunately... This is my result if I use the measure you described:

     

    So what I want to see:

    - Res Nr is already excactly what I want currently in the table: it gives me all bookings made before today 28-01-2019 (so time range is a long time ago until 28-01-2019) with a return in the months jan19-dec19 

     

    - Res Nr LY: I want to know, all bookings made before 28-01-2018  (so time range is a long time ago until 28-01-2018) for the months Jan18-Dec18

     

    It will look something like this (I did this manually in excel by deleting all bookings booked after the today-1 year with a return in 2018:

     

    Thank you all for your help! Let's solve this! :)

    • Stachu's avatar
      Stachu
      Icon for Community Champion rankCommunity Champion
      7 years ago

      try this measure

      Res Nr Today-1YR = 
VAR __CurrentDate = TODAY()
VAR __LastYearDate = EDATE(__CurrentDate,-12)
RETURN
CALCULATE([Res Nr], SAMEPERIODLASTYEAR('Date Exp Return'[Date]), RezReporting[Date Booked 2]<__LastYearDate)

      For Jan it gives 3973 which is different than your calculation but it matches nr of rows in RezReporting, booked before 28-01-2018 (based on RezReporting[Date Booked2]), for Jan 2018 (based on RezReporting[Date Exp Return 2])

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Yes thank you so much! This was exactly what I was looking for!

         

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      7 years ago

      Hi,

       

      I do not understand.  Please let me know how exactly have you arrived at the figures 3654 for 2019-02 and 3630 for 2019-03.  What filers have you applied on the base data to get this result?