Total Bookings Same Days Prior

[bilalali87]

Hey guys,

I have two tables set up, one which shows the dates possible to make bookings in the current year, and one which shows the dates it was possible to make bookings in a previous year. In two separate tables I have the booking details for each years events (both have relationships set up).

What I'm subsequently trying to do is take the days prior to the current years event (based on the current date) and fetch the booking figure from the same days prior in last years figures - so if I'm 7 days out from this years event, I want to see what bookings were like 7 days out from the previous years event to draw comparisons - and display this total in a Card visual.

So far I've tried the below without success;

PrevYearBTD = 
VAR DaysPriorTY = MAX('Current'[Days Prior])
VAR DaysPriorLY = LOOKUPVALUE(DatesPrevious[Days Prior], DatesPrevious[Date], TODAY()-365)
VAR BookingsLY = DISTINCTCOUNT ( 'Previous'[User Id] )

RETURN
IF(DaysPriorLY=DaysPriorTY, BookingsLY, 0)

Any ideas on best way to achieve this?

[v-yalanwu-msft · Accepted solution]

Hi @bilalali87 ,  

You could create a measure by the following formula: 

PrevYearBTD =
CALCULATE (
    DISTINCTCOUNT ( Previous[User Id] ),
    FILTER (
        ALL ( 'Previous' ),
        [Date]
            >= TODAY () - 365 - 7
            && [Date]
                <= TODAY () - 365
    )
)

The final output is shown below:  

If it's not right, please provide me with more details or share me with your .pbix file after removing sensitive data.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  

[bilalali87]

Thanks @v-yalanwu-msft I took your suggestion, tweeked slightly and its now working!

[lbendlin]

There's a fantastic video that you will want to watch.

https://youtu.be/mHb5iyV01D4