Average DAX

[Anonymous]

Hi,

I am getting huge difference between Last X Days Sales(i.e below hundred) and Average Last X Days Sales (aroung 1 million). can any one tell me what is wrong with Average Last X Days Sales measure.

Last X Days Sales =
VAR day =
IF ( ISINSCOPE ( 'Calendar'[Month Number] ), -[NumberOfDays in Current Month],
IF ( ISINSCOPE ( 'Calendar'[Quarter Name] ), -[Number of days in Quarter],
IF ( ISINSCOPE ( 'Calendar'[Date] ), -7, -1 )
))
RETURN
CALCULATE (
SUM(sales[salesDetail.qty]),
DATESINPERIOD ( 'Calendar'[Date], LASTDATE( 'Calendar'[Date] ), day, DAY )
)

Average Last X Days Sales =
VAR day =
IF ( ISINSCOPE ( 'Calendar'[Month Number] ), -[NumberOfDays in Current Month],
IF ( ISINSCOPE ( 'Calendar'[Quarter Name] ), -[Number of days in Quarter],
IF ( ISINSCOPE ( 'Calendar'[Date] ), -7, -1 )
))
RETURN
CALCULATE (
AVERAGEX ( sales, sales[salesDetail.qty]),
DATESINPERIOD ( 'Calendar'[Date], LASTDATE( 'Calendar'[Date] ), day, DAY )
)

[Icey]

Hi @Anonymous ,

I reproduced your question, but didn't meet any issue. Maybe you could try it again. And if there is still any issue, please let me know and give me more details.

Best Regards,
Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

These are the measures that i am using. still getting wrong values. could anyone please modify the below measures.

---

Last X Days Sales =
VAR day =
IF (ISINSCOPE ( 'Calendar'[Year] ), -[NumberOfDays in Current Year],
IF ( ISINSCOPE ( 'Calendar'[Quarter Name] ), -[Number of days in Quarter],
IF ( ISINSCOPE ( 'Calendar'[Month Number] ), -[NumberOfDays in Current Month],
IF ( ISINSCOPE ( 'Calendar'[Date] ), -7, -1 )
)))
RETURN
CALCULATE (
SUMX(sales,sales[salesDetail.qty]),
DATESINPERIOD ( 'Calendar'[Date], LASTDATE( 'Calendar'[Date] ), day, DAY )
)

Average Last X Days Sales =
VAR day =
IF (ISINSCOPE ( 'Calendar'[Year] ),-[NumberOfDays in Current Year],
IF (ISINSCOPE ( 'Calendar'[Quarter Name] ), -[Number of days in Quarter],
IF (ISINSCOPE ( 'Calendar'[Month Number] ), -[NumberOfDays in Current Month],
IF (ISINSCOPE ( 'Calendar'[Date] ), -7, -1 )
)))
RETURN
CALCULATE (
AVERAGEX (sales, salesDetail[salesDetail.qty]),
DATESINPERIOD ( 'Calendar'[Date], LASTDATE('Calendar'[Date] ), day, DAY )
)

[rsimonsen]

This might be as simple as you are using sum instead of sumx.

Yours-

RETURN
CALCULATE (
SUM(sales[salesDetail.qty]),
DATESINPERIOD ( 'Calendar'[Date], LASTDATE( 'Calendar'[Date] ), day, DAY )
)

Change to 

RETURN
CALCULATE (
SUMX(sales[salesDetail.qty]),
DATESINPERIOD ( 'Calendar'[Date], LASTDATE( 'Calendar'[Date] ), day, DAY )
)