Forum Discussion

shinolalady's avatar
shinolalady
New Member
7 years ago
Solved

LY same DAYS

How can i create a measure to calculate PY sales for the same DAY range (rather than DATES)? My data has every individual date of a sale, I'd like to look at Yesterday's sales (2/27/19) vs LY Wednes...
  • OwenAuger's avatar
    7 years ago

    shinolalady 

    For this sort of calculation, I would recommend you have a separate Date table, and include Year and DayOfYearNumber columns such that corresponding weekdays from different years have the same DayOfYearNumber.

     

    The DAX Date Template from SQLBI has such a column called FW DayOfYearNumber.

     

    Here is an example I created:

    Onedrive link

     

    The LY Sales measure looks like this, which you can adapt to your model:

    PY Sales = 
    CALCULATE (
        SUM ( 'Sales'[Sales] ),
        TREATAS (
            SELECTCOLUMNS (
                'Date',
                "FW Year", [FW YearNumber] - 1,
                "FW DayOfYearNumber", [FW DayOfYearNumber]
            ),
            'Date'[FW YearNumber],
            'Date'[FW DayOfYearNumber]
        ),
        ALL ( 'Date' )
    )

    This measure takes the FW Year & FW DayOfYearNumber combinations from the current filter context, and shifts each year to the previous year.