Matrix table - week over week (last year vs this year)

[Anonymous]

Hello Community, 

I have a matrix table where the Years are the columns and Month Week and Day are in the rows.  

My goal is to compare each of these granularities versus the prior year, as the user drills down. 

My month calculation shows the correct value.     Meaning, if I am just looking at January 2020  vs   2021  (period to date), the percentage variance is correct.  

However, if I drill down to the week level, the percent variance gets totally out of whack, as you can see below.    I know this subject can be quite complex but I have a date table, nothing unique like a 4-4-5 calendar, and I am not trying to count only weekdays or anything.  

Any help is appreciated.

[v-yingjl · Accepted solution]

Hi @Anonymous ,

You can try to create this measure to calculate the difference by samplepeoriod last year:

percent =
VAR currentyear =
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER ( 'Table', YEAR ( 'Table'[Date] ) = YEAR ( TODAY () ) )
    )
VAR lastyear =
    CALCULATE (
        SUM ( 'Table'[value] ),
        SAMEPERIODLASTYEAR ( 'Table'[Date].[Date] )
    )
RETURN
     ( currentyear - lastyear ) / lastyear

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , How week drill down created. You have created a week in the date table like

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Number = WEEKNUM([Date],2)
Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])

and you are having measures like

sales = SUM(Sales[Sales Amount])

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8