Missbehaving Matrix Drill down

[StuartJohnson]

Hi,

I'm looking for some help. I have a matrix table that i'm using to count the number of complaints for a given week versus last year. I can get this to work perfectly fine but because i have a completed dataset for last year and not this (i only want to show both years data to date i.e on week 12 i only show last years data up to week 12 even though i have 52 weeks i could show). I dont have to update filters every week so i've been writing measures (as i can't use a measure in a filter) to only show data up to this.

CompTWLY =
VAR MaxW = calculate(max(TotalComplaints[M&S Week]),Calendar[M&S Year] = "2021/22")
return calculate(sum(TotalComplaints[Complaints]), filter('Calendar',Calendar[M&S Year]="2020/21"), filter('Calendar',[Week]=MaxW))

For last year this works fine; However running the same measure but with the year changed does something weird, Initially the data fot This year (CompTWTY) looks correct, although i have 2 extra complaints compared to the total. However if you drill down the totals change and the information becomes more incorrect for this year but is still correct for last year?

Why would the information be incorrect but fine using the similar measure for last year.

If i change MaxW = to be a defined value such as 22 this all works correctly and if i place a card with the formula i'm currently using to define MaxW it shows the week as 22.

 

[RoryA]

Couldn't you use:

calculate(sum(TotalComplaints[Complaints]), Calendar[M&S Year]="2020/21",Calendar[Week]=MaxW)

instead?

 

[StuartJohnson]

Couldn't you use:

calculate(sum(TotalComplaints[Complaints]), Calendar[M&S Year]="2020/21",Calendar[Week]=MaxW)

instead?

Thank you but either way produces the same result. It appears that the part of the measure calculating the MaxW variable isn't working correctly on a row by row basis.

 

[RoryA]

What's the exact measure for CompTWTY? Do you have a sample file you can link to?

 

[StuartJohnson]

THe measure for CompTWTY is almost identical to the working CompTWLY:

CompTWTY =
VAR MaxW = calculate(max(TotalComplaints[M&S Week]),Calendar[M&S Year] = "2021/22")
return calculate(sum(TotalComplaints[Complaints]), filter('Calendar',Calendar[M&S Year]="2021/21"), filter('Calendar',[Week]=MaxW))

I've bolded in Red the only difference between the working measure and the non working measure.

 

[RoryA]

Shouldn't that be 2021/22?

 

[StuartJohnson]

Apologies it is 2021/22, that was a typo on the forum not in the Power BI