Latest Weeks Volume Issue with DAX

[matratus34]

Hi,
This might not be enough detail, but I have sales and volume data (t_volumes) linked to a calendar table (t_calendar) in Power Pivot.
The t_volumes data is at week, customer and product level.
There is also a t_product table linked to the T_volumes table, that contains all my product categories, unit of purchase etc

I'm trying to build a report that includes a column in a pivot for the latest weeks volumes - at different levels e.g Product, Catergory etc


If I hard code the lastest week into my DAX I get the correct results in my Pivot table (when pulling in Category out of my t_product table too).
But If I use a variable to look at the latest week it doesn't work (see below)

What I think is happening is that although there is volume for every week in data, there isn't always volume for every week and Category combination. If the code doesn't find any sales in the latest week for a Category then it brings back the volumes for that category for the latest week that it had sales in.


This code works:

HCLatestWeekVolumes:=CALCULATE(
SUM(t_volumes[Eaches]),
t_calendar[Long week] = 202522)

This code doesn't work

LatestWeekVolumes:=VAR LatestWeek = MAX(t_volumes[Long week])
RETURN
CALCULATE(
SUM(t_volumes[Eaches]),
t_calendar[Long week] = LatestWeek
)


In the data in the screenshot - taking Group 2 as an example - the Category had no sales in the latest week so left column is correct.
The right column (which uses the DAX with the variable brings in a volume of 200 which was the volume the Category had in the previous week.

I've played around with DAX containing the MAX function but I can't get it to work properly - can anyone help ? Thanks

 

[matratus34]

thanks in advance

 

[JEC]

what if you write it like this?
Do you have the correct relationships?

CALCULATE(
  SUM(t_volumes[Eaches]),
   t_calendar[Long week] = MAX(t_volumes[Long week])
 )