Forum Discussion
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 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):
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
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])
7 Replies
v-lili6-msftCommunity Support
hi, Anonymous
If your date table is just until today(), if so, here is a similar post for you refer to:
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
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
Super User
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.
