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])

v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
7 years ago

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'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! :)