Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
Thank you! I've already made it variable
var DayName = MAX('DimDate week'[WeekDay])
[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.
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
@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])),
))
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
53 | |
46 | |
16 | |
12 |