topic Re: Power Pivot - Wrong Results when using MAX of DATE in DAX Commands and Tips

Power Pivot - Wrong Results when using MAX of DATE

matratus28 — Thu, 12 Sep 2024 14:58:52 GMT

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

Re: Power Pivot - Wrong Results when using MAX of DATE

aduguid — Thu, 12 Sep 2024 15:10:23 GMT

Try this measure

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

Re: Power Pivot - Wrong Results when using MAX of DATE

matratus28 — Thu, 12 Sep 2024 16:46:44 GMT

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?

Re: Power Pivot - Wrong Results when using MAX of DATE

aduguid — Thu, 12 Sep 2024 22:00:36 GMT

Are you trying to calculate the previous week?

Re: Power Pivot - Wrong Results when using MAX of DATE

matratus28 — Fri, 13 Sep 2024 07:16:19 GMT

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

Re: Power Pivot - Wrong Results when using MAX of DATE

Selva-Salimi — Fri, 13 Sep 2024 10:20:36 GMT

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.

Re: Power Pivot - Wrong Results when using MAX of DATE

matratus28 — Fri, 13 Sep 2024 12:05:21 GMT

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?

Re: Power Pivot - Wrong Results when using MAX of DATE

Selva-Salimi — Fri, 13 Sep 2024 12:20:39 GMT

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?

Re: Power Pivot - Wrong Results when using MAX of DATE

matratus28 — Fri, 13 Sep 2024 12:24:43 GMT

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

Re: Power Pivot - Wrong Results when using MAX of DATE

Selva-Salimi — Fri, 13 Sep 2024 12:30:29 GMT

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.

Re: Power Pivot - Wrong Results when using MAX of DATE

matratus28 — Fri, 13 Sep 2024 12:43:36 GMT

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