YTD like-for-like: correction for new/closed stores

[Anonymous]

Hi all,

I'm using formulas for YTD this year and last year. The formulas work perfectly fine, but I would like to alter the formulas to take new shop openings and (temporary) closings into account in my calculations.

YTD formulas I currently use:

Revenue excl. VAT this year = TotalYTD([Revenue excl. VAT];'Time Variables'[Date])

Revenue excl. VAT last year = 
VAR MaxDate = 
    Calculate( 
        Max (Transactions[Transaction Date]);all(Time Variables[Date]))
Return
    Calculate(
        [Revenue excl. VAT];DATEADD(DATESYTD(Time Variables[Date]);-1;YEAR);
        SAMEPERIODLASTYEAR(
            INTERSECT(
                VALUES(Time Variables[Date]);
                DATESBETWEEN(Time Variables[Date];BLANK();MaxDate)
        )))

Simplified relational schedule:

Time variables feeds transaction data (single way). Store data feed transaction data (single way).

I'm struggling with how I can implement a filter or variable into my formulas or as a field to only display YTD comparisons of stores that were open both last year as well as this year in my dashboard. Definition of an active store on a date is revenue excl. VAT > 0 for that specific day. 

To be a bit more specific: let's say it is the 20th of January 2020 and I know that store 'New York' has been open on 1 - 8 January +12 - 15 January 2019 and 1-20 January 2020. Then I would only like to include the YTD data of 1-8 January & 12-15 January. I should exclude the data of 9-11 January and 16-20 January for this specific store. Otherwise I would compare 20 full sales days in 2020 with 12 sales days in 2019. My YTD comparison would not be fair if I would compare 20 full days.

All help would be much appreciated!

[amitchandak]

Not tested, but try like this

YTD = TotalYTD([Revenue excl. VAT];'Time Variables'[Date])
LYTD =TotalYTD([Revenue excl. VAT];dateadd('Time Variables'[Date]),-1,year)

YTD new =calculate([YTD],filter(table,not(issblank([LYTD])) && [LYTD] >=0))

In view by store it should give only last year store if you use LYTD and YTD New

[Anonymous]

Hi Amitchandak,

Thanks for your reply! I've tried your suggestion, but couldn't get the last formula to work. In your YTD new, what should I fill in for "table"? 

The LYTD formula works, but it shows the results of the full year last year, while my formula shows the results from 1th of January till the date of today (but then one year ago) - which is what I intended to do.

Personaly, I think it would work best if I can somehow create a field that I can add as a page filter for active stores. Where active stores are defined as stores which generated revenue on a specific date in the current year and in the last year.

[amitchandak]

The table should be Transactions.

You can put a visual level filter to have LYTD >0.  or filter your  YTD formula  to have LTYD >0.

As you are not selecting date and your calendar is beyond today that is why LYD is taking full values.

[Anonymous]

Thanks again for your reply amitchandak! I've been trying with your formulas, but I haven't been able to get it working - I'm a beginner so probably I'm doing something wrong somewhere.

Nonetheless, I think the solution I'm looking for is something more similar to the slicer in the following topic rather than the formula you gave me: https://community.powerbi.com/t5/Desktop/Like-for-Like-Analysis-Slicer/m-p/845757.

And to create such a slicer I think I need to write a measure with the following conditions:

- If [revenue] >0 on [date this year] for [store x]  and

- if [revenue] >0 on [same date last year] for [store x]

- of course there are multiple stores and multiple days. The formula should compare each date/store this year with same date/store last year.

-> If true then "like-for-like sales", if false then "all sales".

Would be very much appreciated if you can help me with that!

[v-chuncz-msft]

@Anonymous 

You may try using CONTAINS in a custom DAX formula.

https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/