Forum Discussion

bo_afk's avatar
bo_afk
Post Patron
6 years ago
Solved

Comparison vs same week last year

I'm trying to compare the sales from this week this year, vs the same week last year (based on week number). Whilst I do have dates in my data, I can't use the SAMEPERIODLASTYEAR function since th...
Need Help
v-alq-msft's avatar
v-alq-msft
Community Support
6 years ago

Hi, bo_afk 

 

Based your description, I created data to reproduce your scenario.

DateTable(a calculated table):

 

 

DateTable = 
CALENDAR(DATE(2019,1,1),DATE(2020,12,12))

 

 

Sales:

There is a one-to-one relationship between two tables.

 

Then you may create measures as follows.

 

Sales this year-week = 
var _date = MAX(Sales[Date])
var _year = YEAR(_date)
var _week = WEEKNUM(_date)
return
IF(
    ISBLANK(MAX(Sales[Date])),
    BLANK(),
    CALCULATE(
        SUM(Sales[Sales]),
        FILTER(
            ALLSELECTED(Sales),
            YEAR(Sales[Date]) = _year&&
            WEEKNUM(Sales[Date]) = _week
        )
    )
)

Sales last year-week = 
var _date = MAX(Sales[Date])
var _lastyear = YEAR(_date)-1
var _lastweek = WEEKNUM(_date)

return

    CALCULATE(
        SUM(Sales[Sales]),
        FILTER(
            ALL(Sales),
            YEAR(Sales[Date]) = _lastyear&&
            WEEKNUM(Sales[Date]) = _lastweek
        )
    )

Year-Week change = 
if(
    ISBLANK(Sales[Sales last year-week]),
    BLANK(),
    Sales[Sales this year-week] - Sales[Sales last year-week]
)

 

 

Result:

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • bo_afk's avatar
    bo_afk
    Post Patron
    6 years ago

    Hi v-alq-msft , thanks for your suggestion. 

     

    I don't think this works for my data as the weeknum function within Power BI is slightly different to how we've defined the week number in our data. Since I already have the weeknum, and yearweek columns in my data, is there a way to reference this in the measure? The user is able to make multiple week selections in the slicer.

     

    Example,

    If the slicer selection is yearweek 202005 - 202007, the measure will then take the values from a year ago, i.e. 201905 - 201907

    Note: there is a many-to-one relationship between the calendar(date) table with the sales table as there is also a market column in the sales table so a date will appear on multiple rows for each market.

    • PaulDBrown's avatar
      PaulDBrown
      Community Champion
      6 years ago

      bo_afk 

      One way to solve this is to add a period index in your date table for your week numbers, and the use this in the filter statement in your measure along the lines of.

      Same week last year = CALCULATE([your measure],
      FILTER(Date Table,
      Date Table [period index] = SELECTEDVALUE(Date Table[period index]) -52))

       

      For multiple selections use the MIN and MAX to establish the range.

      EDIT: you might have to inlude a variable to cater for years with 53 week periods!

       

      Here is an example:

      YearWeek Table (I've actually created a new table) as in:

       

       

       

      to set up the model like this:

       

      And the build you measures and visuals referencing this YearWeek table, such as:

       

       

      Sum of Forecast LY = 
VAR Weeksinyear = CALCULATE(MAX('YearWeek Table'[Week of Year]); 
                    ALLEXCEPT('YearWeek Table'; 'YearWeek Table'[Year]))
Return
CALCULATE([Sum Forecast];
FILTER(ALL('YearWeek Table');
'YearWeek Table'[YearWeek Index] = SELECTEDVALUE('YearWeek Table'[YearWeek Index]) - Weeksinyear))

       

       

       

      To get you this:

       

  • Tani4ka's avatar
    Tani4ka
    Helper II
    5 years ago

    Hi There,

     

    I tried to use the formula you provided but it returns wrong results + wrong totals in my case. See below:

    I have created a different measure that returns correct results but it doesn't sum up :(. See Below:

    In this case, I have 2 tables: 

    1. Calendar - 'Date' table

    2. Job_Alerts

     

    Any suggestion on how I can adopt your DAX to show correct results & correct totals or amend mine, to sum up?

    Thank you kindly for any help.

     

    Many thanks

    T