Calculate the average of a rolling fixed day

[Anonymous]

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

[v-jianboli-msft]

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.

[Anonymous]

Thank you! I've already made it variable

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

[daXtreme · Accepted solution]

[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.

[Anonymous]

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

[amitchandak]

@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])),
))

or

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

[Anonymous]

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.