StuartJohnson
New Member
- Joined
- Sep 13, 2021
- Messages
- 4
- Office Version
- 365
- Platform
- Windows
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.
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.