topic Re: Calculate the average of a rolling fixed day in DAX Commands and Tips

Calculate the average of a rolling fixed day

Anonymous — Thu, 08 Sep 2022 07:01:25 GMT

How can you calculate the average of a fixed day? For example, only the average of the Mondays of the year. Below I have already made a calculation in which he takes the average of the course of days. I want to get this same rhythm from only fixed days.

measure test =

CALCULATE(

AVERAGEX(FILTER(ALLSELECTED('DimDate'),

'DimDate'[Date] <= MAX('DimDate'[Date])),

[value sum]))

Re: Calculate the average of a rolling fixed day

amitchandak — Thu, 08 Sep 2022 08:15:22 GMT

Anonymous , You need have weekday and year column in date table with a measure value sum in model

try measure like

CALCULATE(
AVERAGEX(ALLSELECTED('DimDate'),[value sum])
Filter( 'DimDate' , ('DimDate'[weekday]) = (MAX('DimDate'[weekday])) && ('DimDate'[year]) = (MAX('DimDate'[year])),
))

CALCULATE(
AVERAGEX(ALLSELECTED('DimDate'),[value sum])
Filter( 'DimDate' , weekday('DimDate'[Date]) = weekday(MAX('DimDate'[Date])) && year('DimDate'[Date]) = year(MAX('DimDate'[Date])),
))

Re: Calculate the average of a rolling fixed day

daXtreme — Thu, 08 Sep 2022 08:42:15 GMT

[Measure for Mondays] = // This variable can be made dynamic and can be harvested // from an independent slicer. Here, I've hard-coded the // day name but you get the gist, I hope. var DayName = "monday" var CurrentlyVisibleMaxDate = MAX( 'DimDate'[Date] ) var Output = CALCULATE( AVERAGEX( FILTER( ALLSELECTED( 'DimDate' ), 'DimDate'[Date] <= CurrentlyVisibleMaxDate, // Also to test, wrap this line in KEEPFILTERS to see // what's gonna happen, like so: // KEEPFILTERS( 'DimDate'[Day Name] = DayName ) 'DimDate'[Day Name] = DayName ), [value sum] ) ) return Output

or this one (should be faster):

[Measure for Mondays] = // This variable can be made dynamic and can be harvested // from an independent slicer. Here, I've hard-coded the // day name but you get the gist, I hope. // By the way, DAX is case-insensitive. var DayName = "monday" var CurrentlyVisibleMaxDate = MAX( 'DimDate'[Date] ) var Output = CALCULATE( AVERAGEX( VALUES( DimDate[Date] ), [value sum] ), 'DimDate'[Date] <= CurrentlyVisibleMaxDate, // Also to test, wrap this line in KEEPFILTERS to see // what's gonna happen, like so: // KEEPFILTERS( 'DimDate'[Day Name] = DayName ) 'DimDate'[Day Name] = DayName, ALLSELECTED( 'DimDate' ) ) return Output

The second example is how it should be really written. First, the simple expression, then all the filters and directives to CALCULATE. This structure ensures not only clarity but also the fastest execution.

Re: Calculate the average of a rolling fixed day

Anonymous — Thu, 08 Sep 2022 09:17:20 GMT

Thank you for your answer. The total correct of the answer only the measure does not apply to the records. In the rightmost column is how it should be.

Re: Calculate the average of a rolling fixed day

v-jianboli-msft — Wed, 14 Sep 2022 09:22:01 GMT

Hi Anonymous ,

Approve with daXtreme , if you want make it variable, you can change the value of

var DayName

Here is my solution:

Based on your description, I have created a simple sample:

Measure = AVERAGEX(FILTER(ALL(DimDate),WEEKDAY([Date],2)=1&&[Date]<=MAX('DimDate'[Date])),[value sum])

You can change the fix day by changing WEEKDAY([Date],2)=1 .

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Calculate the average of a rolling fixed day

Anonymous — Wed, 14 Sep 2022 09:28:30 GMT

Thank you! I've already made it variable

var DayName = MAX('DimDate week'[WeekDay])

Re: Calculate the average of a rolling fixed day

Anonymous — Thu, 29 Sep 2022 06:02:49 GMT

Hi Daxtreme,

I made the [measure for monday] variable.

var DayName = MAX('DimDate week'[WeekDay]

How can you add up the outcome of the measure [measure for mondays]? So the figures from Monday to Sunday added together. To use it in a matrix.

Thank you