YOY for only those weeks present in current yrear

[vk_18]

Hi 

I have  sales table which has data from different retailers (eg- A,B,C)
now i want yoy growth of sales in such a way that it compares the weeks of current year and calculate yoy for that.
for example for current year/max year there is data only till may,
now for this want to calculate yoy growth for sales till may in this year from sales till may last year
while for rest of the years  it should be norrmal yoy growth.
also different retailers have different data( like for A data is there till may(20 weeks), for B it is there till feb(10 weeks) and for c it is there till april-13 weeks)

i have a slicer of retailer and year on my page , when is select year 2023( max year) and select retailer A i want yoy growth till may from last year sales till may, on selecting B it should be till feb to feb of lasy year and so on.

i did use the below dax but i think it is taking the max weeks of overall data (20)
can anyone help me with max weeks for each retailers-

Last year sales =

Var Maxyr = CALCULATE(MAX(DateTable[Year]), ALL(DateTable))

VAR MaxWeekCurrentYear = MAXX(FILTER(DateTable, DateTable[Year] = Maxyr), DateTable[WeekNo])

RETURN

CALCULATE(

    SUM('Combined Data'[sales]),

    DateTable[Year] =Maxyr-1,

    DateTable[WeekNo] <= MaxWeekCurrentYear

)

i have 2 tables combined data and datetable connected by a date key and retailers column is in combined data

[Martin_D]

Hi @vk_18 ,

Assuming your date key is a number that is chronologically counting upwards (something like 1, 2, 3, ... or 20221230, 20221231, 20230101, ...) with one key per day (not week), this code solves your requirement. It checks per retailer which is the last week in the current year and then also includes only the previous year's sales up to that week, individually for each retailer.

Last year sales YTD (week based) = 
VAR _LastDateKeyWithSales = MAX ( 'Combined Data'[DateKey] )
VAR _CurrentYear = 
    CALCULATE ( 
        SELECTEDVALUE ( 'DateTable'[Year] ), 
        'DateTable'[DateKey] = _LastDateKeyWithSales, 
        ALL ( 'DateTable' ) 
    )
VAR _MaxWeekNumberOfCurrentYearInContext =
    CALCULATE (
        MAX ( 'DateTable'[WeekNo] ),
        'DateTable'[Year] = _CurrentYear
    )
VAR _MaxSalesWeekByRetailer =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZECOLUMNS (
                'Combined Data'[retailer]
            ),
            "@MaxSalesWeek",
            VAR _MaxSalesDateCurrentYearByRetailer = 
                CALCULATE ( 
                    MAX ( 'Combined Data'[DateKey] )
                )
            VAR _WeekNoOfDateKey = 
                CALCULATE ( 
                    SELECTEDVALUE ( 'DateTable'[WeekNo] ), 
                    'DateTable'[DateKey] = _MaxSalesDateCurrentYearByRetailer, 
                    REMOVEFILTERS ( 'DateTable' ) 
                )
            RETURN
            _WeekNoOfDateKey
        ),
        // make sure all last sales dates are from the same calendar year, because at the beginning of a new year,
        // for some retailers there might be last data from last year and for some already from new year,
        // and we don't want to compare a mixture of current years and previous years (do we?)
        'DateTable'[Year] = _CurrentYear,
        'DateTable'[WeekNo] <= _MaxWeekNumberOfCurrentYearInContext
    )
VAR _PreviousYearSalesUpToMaxWeekCurrentYearByRetailer =
    ADDCOLUMNS (
        _MaxSalesWeekByRetailer,
        "@PYTDsales",
        CALCULATE (
            SUM ( 'Combined Data'[sales] ),
            REMOVEFILTERS ( DateTable ),
            'DateTable'[WeekNo] <= EARLIER ( [@MaxSalesWeek] ),
            'DateTable'[Year] = _CurrentYear - 1
        )
    )
RETURN
SUMX ( _PreviousYearSalesUpToMaxWeekCurrentYearByRetailer, [@PYTDsales] )

The report with sample data looks like this:
PYTD by category

You can download the sample solution here.

Contraints: In a situation that might occur around new year, there might be data from some retailers for the new year already and for other only for previous year. In this case, no data for retailers is included for the previous year if there is also no data for the current year. If you want instead to define "previous year" individually per retailer, e.g. the Last year sales total should included data from year 2022 for retailer A and data from year 2021 for retailer B if there is data for retailer A up to year 2023 and for retailer B only up to 2022, then you need to change the code.
You cn doenload a file showinf the around new year situation here.
BR
Martin