Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Total Bookings Same Days Prior

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?

 

 

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    Hi Anonymous ,  

     

    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.  

3 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,  

     

    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.  

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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