Reputation: 83
I am comparing the average of daily-rates between two time periods. However, Ave_#curr gave me the average of all rates, and Ave_#prev gave me the divided results of the numerator sum over the denominator sum. How can I get the average daily rates for both time periods? All comments are highly appreciate it.
'''
Ave_#curr =
var _days = SELECTEDVALUE('Day Range Table'[Days])
var ReferenceDate = MAX('Date'[_dt])
var PreviousDates =
DATESINPERIOD(
'Date Compare'[_dt],
ReferenceDate,
-_days,
DAY
)
var ave_ = CALCULATE(
AVERAGEX(VALUES('cod'[_dt1]), 'Measure Table'[curr_#]),
REMOVEFILTERS('Date'),
KEEPFILTERS(PreviousDates),
USERELATIONSHIP('Date'[_dt], 'Date Compare'[_dt])
)
var Result =
CALCULATE(
AVERAGEX(VALUES(cod[_dt1]), ave_),
REMOVEFILTERS('Date'),
KEEPFILTERS(PreviousDates),
USERELATIONSHIP('Date'[_dt], 'Date Compare'[_dt])
)
return
Result
Ave_#prev =
var _days = 180
var dayrange = SELECTEDVALUE('Day Range Table'[Days])
var StartDate = MAX('Date'[_dt]) - _days
var PreviousDates =
DATESINPERIOD(
'Date Compare'[_dt],
StartDate,
-dayrange,
DAY
)
var ave_ = CALCULATE(
AVERAGEX(VALUES('cod'[_dt1]), 'Measure Table'[prev_#]),
REMOVEFILTERS('Date'),
KEEPFILTERS(PreviousDates),
USERELATIONSHIP('Date'[_dt], 'Date Compare'[_dt])
)
var Result =
CALCULATE(
AVERAGEX(VALUES(cod[_dt1]), ave_),
REMOVEFILTERS('Date'),
KEEPFILTERS(PreviousDates),
USERELATIONSHIP('Date'[_dt], 'Date Compare'[_dt])
)
return
Result
'''
Upvotes: 0
Views: 25