Filter on same day last year

[Anonymous]

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 rental business. That means I have three dates available for which I have created seperate date tables:

- Date booked
- Date Pickup

- Date Exp Return (which means expected return)

I have linked the relationships to the main database (called RezReporting). Then, I created a simple measure to count the amount of reservations (each row is a unique reservation):

Res Nr = COUNTROWS(RezReporting)

Because bookings for car rentals are made in advance, I made an ''orderbook'' in which I can see how many reservations I already have in the system with a return in the coming months in the 2019: a matrix that shows the Res Nr per month in 2019 from the date table Date Exp Return.

Now I want a measure that shows me the in the next column in the matrix for the same months last year how we booked until the same day perspective but one year ago (24-01-2018) for the months one year ago (jan-dec 2018).

So for example. From today's perspective 24-01-2019 we have 1.199 reservations already booked with an expected return in the month 02-2019. Now I want to know how many reservations we had booked until 24-02-2018 with an expected return in the month 02-2018.

Can someone help me please with this? The formulas CALCULATE() and SAMEPERIODLASTYEAR() will give me the final result for the whole month instead of an accumulated result until today's date.

Thank you all so much!

Merel

[v-lili6-msft]

hi, @Anonymous

If your date table is just until today(), if so, here is a similar post for you refer to:

https://community.powerbi.com/t5/Desktop/SAMEPERIODLASTYEAR-DATESYTD-different-from-DATESYTD/td-p/594650/page/2

please try this way as I have provided:

date table is from "2018,1,1" to today, you may try this formula

Date Table = ADDCOLUMNS (
CALENDAR (DATE(2018,1,1), DATE(YEAR(TODAY()),12,31)),
"Year", YEAR ( [Date]),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Monthnumber", FORMAT ( [Date], "MM" )
    )

 add a judge column for date table

judge = IF('Date Table'[Date]<=TODAY(),1,2)

Then add it to report level filter and set it "1"

If not your case, please share your simple sample pbix file or some data sample and expected output. 

Best Regards,

Lin

[Anonymous]

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]

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]

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 · Accepted solution]

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]

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

[Ashish_Mathur]

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?