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 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!