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.
Hi @crisher ,
If you only calculate the average for rows with data, then try the formula of @DataInsights .
If you want to calculate the average for all weekdays in current month, try the following formula.
Measure =
AVERAGEX (
ADDCOLUMNS (
CALCULATETABLE (
'Calendar',
FILTER (
'Calendar',
'Calendar'[WeekendOrHoliday] = 0
&& FORMAT ( 'Calendar'[Billing Date], "YYYYMM" ) = FORMAT ( TODAY ( ), "YYYYMM" )
)
),
"BillingTotal", COALESCE ( [Billing Total], 0 )
),
[BillingTotal]
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Try this measure:
MTD Daily Average =
CALCULATE (
AVERAGEX ( 'Calendar', [Billing Total] ),
DATESMTD ( 'Calendar'[Billing Date] ),
'Calendar'[WeekendOrHoliday] = 0
)
Proud to be a Super User!
Hey @DataInsights, I think this is close. But, I do need the billings that are occurring over the weekend as part of Mondays' average calculation. Does that make sense?
Actually, I don't think this is right. At the moment, it is simply doing the average of that day. The intent is to do a running average for the month and remove holidays and weekends. Right now, it is just doing the average of that single day. See below...
As I understand it, you want to include all billings (numerator), but exclude weekends and holidays from the count of days (denominator). Revised measure:
MTD Daily Average =
// include weekends and holidays
VAR vBillings =
CALCULATE ( [Billing Total], DATESMTD ( 'Calendar'[Billing Date] ) )
// exclude weekends and holidays
VAR vDays =
CALCULATE (
COUNTROWS ( 'Calendar' ),
DATESMTD ( 'Calendar'[Billing Date] ),
'Calendar'[WeekendOrHoliday] = 0
)
VAR vResult =
DIVIDE ( vBillings, vDays )
RETURN
vResult
Proud to be a Super User!
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 |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |