Power Pivot - Wrong Results when using MAX of DATE

[matratus28]

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

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):

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

)

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

[Selva-Salimi]

Hi @matratus28 

I  think the problem is that you use max for t_volume table's week column which doesn't work correct. you should use t_calendar table instead. ( note that the relation between t_volume and t_calendar should be single filter direction)

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly. 

[matratus28]

Thanks for the reply - the dates in my t_calendar table go into the future so using the max for that table bring back 0 as the date hasn't passed yet and so there are no sales.

Maybe I should amend my query so that the calendar table refreshs each week but only upto the lastest week?

[Selva-Salimi]

@matratus28 

you have some solution to manage this. but it is not clear for me that did you want do select latest week based on any slicer selection? or is it based on todays date?

[matratus28]

It's not based on slicer selection. It's based on the data in the data model. I want to calculate total volumes based on the last week but the last week is based on the latest date in the data that has been loaded.

The date format in YYYYWW

So if there is volume for 202401, 202402 and 202403 - I just want volumes 202403

[Selva-Salimi]

Ok, then you can write this to find max_date:

CALCULATE( MAX(t_calendar[Date]), FILTER(t_calendar, CALCULATE(COUNTROWS(t_volume), t_volume[Date] = t_calendar[Date]) > 0))

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly. 

[matratus28 · Accepted solution]

This gives me a SEMANTIC ERROR - thanks for trying though.

I realise how difficult it is when someone is just describing what they want.

I've got a work around now using a calendar table that only runs upto the latest week in the data

[aduguid]

Are you trying to calculate the previous week?

[matratus28]

Yes - it will always be the previous week.

I have a "Week" column in YYYYWW format and a "First Day of Week" column in DD/MM/YYYY format

[aduguid]

Try this measure 

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

[matratus28]

I think what is happening is that when I bring in total volumes to my pivot table then the figure is correct.

But if I bring in another field to split the volumes - in this case Core List Group - then the pivot gives me the latest weeks volumes for each Group with sales in the latest week, but if there were no sales in the latest week then it gives me sales for that Group from the last week that it had sales.

Any idea how to get round that?