Comparison vs same week last year

[bo_afk]

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 the same dates a year ago aren't necessarily the same week number (example below).

I do have a date table joined to my sales table (on date)

Date	Year	WeekNumber	YearWeek
26/01/2019	2019	4	201904
26/01/2020	2020	5	202005
...	...	...	...

Sales table

Date	Sales
26/01/2019	100
26/01/2020	200

Based on the slicer selection for date (on year and week number), I would like to display
- Sales this year (slicer selection)

- Sales last year

- Year on year change

Any ideas on how this can be done?

Many thanks!

afk

[v-alq-msft]

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.

[Tani4ka]

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

[bo_afk]

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]

@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:

[bo_afk · Accepted solution]

All, I finally managed to find a solution here in this link https://forum.enterprisedna.co/t/same-period-last-year-for-fiscal-week-nbr-weeks-in-sales-table-are-...

What I had to do was to create a measure based on the measure that you all had suggested.

[amitchandak]

Create a date calendar as given in the links and following functions can help

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
2 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Year))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[PaulDBrown]

@bo_afk 

You need to set up a Calendar table (either in Power Query or using DAX functions CALENDAR() or CALENDARAUTO()), link the date fields in a one-to-many relationship from the Calendar table to your data table and then use the Calendar date field for your time intelligence calculations (and for measures, slicers and filters for that matter).

For time intelligence functions to work, you require a set of continuous dates (unique) which obviously must cover the whole range of dates in your data.