Booking and Travel date shenanigans

[DuncanP]

Hi

I'm really hoping that someone out there might have done something similar to what I'm trying to do.

I'm working on YoY revenue calculations for a travel company. They have bookings which have a booking date and a travel date. I want to show this year's revenue vs last year's revenue, but have it display by travel date. At the same time, in order to give a fair comparison, I want the last year calculation to include only bookings that had been made up to the same day last year. In other words, something like this:

Revenue TY/LY at September 17:

Month of Travel	Revenue	Revenue LY
September	£234,999	£194,838
October	£76,877	£68,948
November	£43,494	£41,949
December	£32,674	£29,598

Obviously, a standard LY calculation would show much higher numbers for Revenue LY, as it would include bookings that were made throughout September to December. But in order to see how we are doing versus the same point last year, we only want to include bookings that had been made before one year ago today.

I've tried adapting the calculations on Dax Patterns but with no luck so far.

I'm sure I can't be the first person to want to do this sort of thing, so if there's anyone out there who's done something similar, or knows someone who has, I'd love to hear from you.

[DuncanP]

So, an update.

I managed to create an adjusted revenue measure that does what I wanted in terms of calculating the figure at an equivalent point each year. I haven't yet managed to work out a corresponding PY calculation, but it seems OK for being able to show all years with fair comparisons between them. The code for the measure is:

Revenue Adjusted = 
SUMX (
    /* sum each fiscal year separately */
    VALUES ( 'Date'[Fiscal Year Number] ),
    /* get the last booking date available (could use another date table to allow the user to select this value) */
    VAR LastBookingDate =
        CALCULATE ( MAX ( Travel[BookingDateKey] ), ALL ( Travel ) )
    /* get the fiscal day of year for the last selected date */
    VAR FiscalDayOfYear =
        LOOKUPVALUE (
            'Date'[Day of Fiscal Year Number],
            'Date'[DateKey], LastBookingDate
        )
    /* get the current fiscal year being aggregated */
    VAR CurrentFiscalYear = 'Date'[Fiscal Year Number]
    /* work out the date in the current year that corresponds to the fiscal day */
    VAR CutoffDate =
        LOOKUPVALUE (
            'Date'[DateKey],
            'Date'[Day of Fiscal Year Number], FiscalDayOfYear,
            'Date'[Fiscal Year Number], MAX ( 'Date'[Fiscal Year Number] )
        ) 
    /* get a table of fiscal days of year for summing up (I think) */
    VAR DaysSelected =
        CALCULATETABLE (
            VALUES ( 'Date'[Day of Fiscal Year Number] ),
            REMOVEFILTERS (
                'Date'[Working Day],
                'Date'[Day of Week],
                'Date'[Day of Week Number]
            )
        )
    RETURN
        /* calculate the revenue for the currently selected fiscal year across each day filtering the travel table by the cutoff date corresponding to that fiscal year */
        CALCULATE (
            [Revenue],
            'Date'[Fiscal Year Number] = CurrentFiscalYear,
            DaysSelected,
            FILTER ( ALL ( Travel ), Travel[BookingDateKey] < VALUE ( CutoffDate ) ),
            ALLEXCEPT ( 'Date', 'Date'[Working Day], 'Date'[Day of Week] )
        )
)

And the corresponding Power BI file is here 

[Anonymous]

Try this code this will calculate your values as compared to today in last year.

PY Revenue =

VAR selectedtravelkey =

CALCULATE (

CALCULATE (

MAX ( Travel[BookingDateKey] ),

USERELATIONSHIP ( 'Date'[DateKey], Travel[BookingDateKey] )

),

DATEADD ( 'Date'[Date], -1, YEAR )

)

VAR today23 =

YEAR ( TODAY () ) - 1

& FORMAT ( TODAY (), "MM" )

& FORMAT ( DAY ( TODAY () ), "DD" )

RETURN

CALCULATE (

CALCULATE (

[Revenue],

DATEADD ( 'Date'[Date], -1, YEAR ),

FILTER ( ALL ( Travel ), Travel[BookingDateKey] < VALUE ( today23 ) )

)

)

[DuncanP]

@Anonymous  - wow, thank you! That is working for the previous year. I've removed the bits that were not being used:

Revenue PY = 
VAR today23 =
    YEAR ( TODAY () ) - 1
        & FORMAT ( TODAY (), "MM" )
        & FORMAT ( DAY ( TODAY () ), "DD" )
RETURN
    CALCULATE (
        [Revenue],
        DATEADD ( 'Date'[Date], -1, YEAR ),
        FILTER ( ALL ( Travel ), Travel[BookingDateKey] < VALUE ( today23 ) )
    )

So in your other reply you asked about Sept 14th. What I want to do is go back one fiscal year, rather than one calendar year. So now I need a way to calculate today23 as one fiscal year back, and change the filter in the calculate statement to also go one year back.

I'll give that a go today and let you know how I get on.

[Anonymous]

If this resolve your query please give kudos 😊. Thanks in advance.

[DuncanP]

@Anonymous 
You can see in my post below that I managed to extend the calculation to a general one that works out the equivalent revenue in any year. I didn't manage to get a PY calculation to work in the end as I am using a week based calendar, not a normal one so can't use the DATEADD bit to go back in time. I haven't yet worked out how to translate the measure into a 364 day calendar so that's still to do.

[Anonymous]

ou tell me september 14th from where you picking up. What is the condition for that date.

[Anonymous]

Is your issue resolved?

[DuncanP]

@Anonymous No, it's not, and I've still got no idea where to start. I've created a Power BI file here that illustrates the problem, and I've described the issue in my reply to @Anonymous.

[Anonymous]

@DuncanP 

That's not clear. I'd suggest that you create a very simple example with just several bookings (maybe even 2 will suffice?) and show here how the calculation you want would work step by step. Then I'm sure it'll be much easier to get the idea about what you want and you'll get an answer almost in no time. Just show a good and simple example. The simplest there is to demonstrate the issue.

[DuncanP]

Hi @Anonymous 

Thanks for replying. I've put a file here that shows the issue. It contains bookings that were made up to Sept 13 2021. On the left is a visual showing this year and the previous year by travel date. On the right is a visual filtered to last year, and for bookings before made Sept 14 2020.

I want the PY measure to be able to show the same figures as the visual on the right.

But you can see that the PY calculation gives the same figures up to August, but from September onwards it is gradually further and further away.

Can you see what I'm trying to do now?