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,
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 )
)
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.
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 )
)
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 )
)
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 |
---|---|
107 | |
99 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |