newlearner101
newlearner101

Reputation: 83

The average results are calculated differently by the same average measure in the DAX for different time periods

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. enter image description here

'''
 
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

Answers (0)

Related Questions