DAX Calculate evaluation order

[dicken]

Hi,

I have a problem understanding a measure ;

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.

 

[Matt Allington]

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.

The Optimal Shape for Power BI Data

It is common for self taught Power Pivot users to get the shape of their data all wrong. This article describes the optimal way to do it.

exceleratorbi.com.au

 

[dicken]

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.

The Optimal Shape for Power BI Data

It is common for self taught Power Pivot users to get the shape of their data all wrong. This article describes the optimal way to do it.

exceleratorbi.com.au

Hello Matt,
Yes, it is just ;

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 ;

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

SUMX(VALUES(Table1[Date]),[Total Sales] )

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

Richad

 

[dicken]

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.

The Optimal Shape for Power BI Data

It is common for self taught Power Pivot users to get the shape of their data all wrong. This article describes the optimal way to do it.

exceleratorbi.com.au

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.