Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi All,
I have written a measure in which the average is calculated for days, months, quarters as well as years but while drilling up to weeks the average is not being calculated. The following is the measure
Recevied Amount calc1 =
var MinDateselected =
CALCULATE(
MIN( 'Date'[Date] ),
ALLSELECTED( 'Date'[Date] )
)
var MaxDateselected =
CALCULATE(
Max( 'Date'[Date] ),
ALLSELECTED( 'Date'[Date] )
)
var Days = DATEDIFF(
MinDateselected,
MaxDateselected+1,
DAY)
VAR _receAmt = CALCULATE(SUM(Payments[ReceivedAmount]),FILTER('Date','Date'[Date] >= MinDateselected && 'Date'[Date]<=MaxDateselected))
Return IF(AND(ISFILTERED('Date'[Date]),(ISBLANK(_receAmt) && MIN('Date'[Date]) >= MinDateselected && MAX('Date'[Date]) <= MaxDateselected)),0, _receAmt)
Thanks,
Prema
Solved! Go to Solution.
@PremaBoddeda , You need try a measure like
Daily = averageX(Values(Date[Date]),calculate(Sum(Table[Value])) )
Monthly= averageX(Values(Date[Date]),calculate(Sum(Table[Value])) )
Then have a measure, in the reverse order of drill, the lowest level first
Switch(True(),
isinscope(Date[Date]), [Daily],
isinscope(Date[Month Year]), [Monthly],
// Add other
[Yearly]
)
IsInScope - Switch Measure at different levels: https://youtu.be/kh0gezKICEM
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
@PremaBoddeda , You need try a measure like
Daily = averageX(Values(Date[Date]),calculate(Sum(Table[Value])) )
Monthly= averageX(Values(Date[Date]),calculate(Sum(Table[Value])) )
Then have a measure, in the reverse order of drill, the lowest level first
Switch(True(),
isinscope(Date[Date]), [Daily],
isinscope(Date[Month Year]), [Monthly],
// Add other
[Yearly]
)
IsInScope - Switch Measure at different levels: https://youtu.be/kh0gezKICEM
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
84 | |
83 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |