Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Average DAX

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

Need Help

3 Replies

  • rsimonsen's avatar
    rsimonsen
    Frequent Visitor
    6 years ago

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

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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