Same week last year doesn't work for all years

[Tani4ka]

Hi All,

I'm trying to solve a problem of the same week last year measure not working correctly. As you can see from the snap below it doesn't show any data opposite to 2021 although data is there.

This is my measure for the same week last year calcs:

There are no filters so not sure what could it be. Any help would be highly appreciated.

Many thanks

T

[amitchandak]

@Tani4ka ,

it Better to create a week rank column.  In you date or week table(Create a separate one)

Create column like

Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense)

Measures like
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[AllisonKennedy]

@Tani4ka  Is [Sessions_Total] a measure? Your DAX should work as written and works in my test scenario, but something strange is happening with your Week Number, as you shouldn't have a result for the Year subtotals. 

Here is my test result. I have used Variables in the below measure, but get the exact same results pictured when using your exact measure: 

Can you please provide more info on what fields you're using in the matrix, what your date table looks like and how it's related, and if [Sessions_Total] is a measure please provide the DAX for that too. 

[Tani4ka]

The issue was that some of the data was in the wrong format in Excell since multiple people add different points.

The new issue I have with it that total for Sessions_LY doesn't show. 

Any suggestion of what could be wrong?

Many thanks

T

[Tani4ka]

Thanks a lot for a speedy response. I think I may know where the issue sits but still not sure how to resolve it.

In my model I have a few tables: 

1. has just one column with a brand name and is connected to other tables

2. date table

3. a few tables with diff data types but all have a matching brand column.

What I noticed if I remove brand filter which is from 'Brand' table all measures work. when I select a specific brand than it fails to work.  Since my data is in Excell I believe there's a formatting issue which I can't resolve. Any suggestion of what to do here?

Many thanks

[amitchandak]

@Tani4ka , what is the formula you tried?

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