DAX Calculate evaluation order

dicken

Active Member
Joined
Feb 12, 2022
Messages
292
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have a problem understanding a measure ;

Excel Formula:
Rolling Average:=CALCULATE([AvSales],FILTER(ALL(Table1[Date]), Table1[Date]<= MAX(Table1[Date])))

This will give the average for each date , so if the date range is 01/01/2002 - 05/01/2020, but there are 5 1st jan, the average sales measure would be divided by 9, which is fine but I can't see how the evaluation is happening, the table is filtered down to a unique list of dates thanks to ALL ,
so, does that mean the AVERAGE( Sales) overrides this as it would include all the dates, I've figures a couple of ways to just average over 'each unique' date but I'd like to find out how this is working. Any pointers to articles or whatever welcome.
Richard.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
It is not possible to say as you have not provided a formula for AvSales. If I assume it is =AVERAGE(Table1[sales]) or similar, then you will have a problem. You need to build a star schema, specifically, add a calendar table and joint it to Table1[Date]. Then use the calendar[date] in your formula and visual. It will then give you the average lifetime to date. The average of all transactions (by count of transactions) for all dates up to and including the last date in the filter context.

 
Upvote 0
It is not possible to say as you have not provided a formula for AvSales. If I assume it is =AVERAGE(Table1[sales]) or similar, then you will have a problem. You need to build a star schema, specifically, add a calendar table and joint it to Table1[Date]. Then use the calendar[date] in your formula and visual. It will then give you the average lifetime to date. The average of all transactions (by count of transactions) for all dates up to and including the last date in the filter context.

Hello Matt,
Yes, it is just ;
Excel Formula:
AVERAGE(Table![Sales] )
,

I was more interested in what the 'CALCLATE' is getting as it would be good to know as it could be useful as there may be situations where you want all whatever taken into account; it's using all of the date from the table not just those visible in the pivot, the easiest way I've Found seems to be to us AverageX ;

Excel Formula:
AVERAGEX(FILTER(ALL(Table1[Date]),Table1[Date]<= MAX(Table1[Date])) ,[Tsales])

Which does give a result by the aggrated dates in pivot table or to use the first formula but a new average measure using sumx so

Excel Formula:
SUMX(VALUES(Table1[Date]),[Total Sales] )

, and then put this inside the calc ( filter etc.

Richad
 
Upvote 0
It is not possible to say as you have not provided a formula for AvSales. If I assume it is =AVERAGE(Table1[sales]) or similar, then you will have a problem. You need to build a star schema, specifically, add a calendar table and joint it to Table1[Date]. Then use the calendar[date] in your formula and visual. It will then give you the average lifetime to date. The average of all transactions (by count of transactions) for all dates up to and including the last date in the filter context.

I made date table and connected as you said to the date column of Table1, but I still give an average of all the dates, not the visible the distinct dates, it doesn't behave any differenct from using the dates from the sales table when using AVERAGE(Table1[Sales] )
As can be seen averge over the calendar is sill looking at all the dates not just those present.
just to not the RTM on the left isn't working because i just changed it to using the calendar table.
Richard.
1664291880139.png
 
Upvote 0

Forum statistics

Threads
1,223,626
Messages
6,173,412
Members
452,514
Latest member
cjkelly15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top