Forum Discussion

PBINewbie920's avatar
PBINewbie920
Helper I
4 years ago
Solved

Average Monthly Sales

Hi,   I needed some help creating a measure/column to calculate the avg monthly sales:   for instance For  Jan 1 = 5/1 = 5 Jan 2 = (5+10)/2 = 7.5 Jan 3 = (5+10+15)/3 = 10 etc.   Any tips?  ...
Need Help
  • Samarth_18's avatar
    Samarth_18
    4 years ago

    PBINewbie920 , Please try this:-

    Measure =
VAR _totalsum =
    CALCULATE (
        SUM ( 'Table (3)'[Sales] ),
        FILTER (
            ALL ( 'Table (3)' ),
            'Table (3)'[Date]
                >= DATE ( YEAR ( MAX ( 'Table (3)'[Date] ) ), MONTH ( MAX ( 'Table (3)'[Date] ) ), 1 )
                && 'Table (3)'[Date] <= MAX ( 'Table (3)'[Date] )
        )
    )
RETURN
    _totalsum / DAY ( MAX ( 'Table (3)'[Date] ) )

    Output:-

     

    Thanks,

    Samarth

Samarth_18's avatar
Samarth_18
Community Champion
4 years ago

PBINewbie920 , Please try this:-

Measure =
VAR _totalsum =
    CALCULATE (
        SUM ( 'Table (3)'[Sales] ),
        FILTER (
            ALL ( 'Table (3)' ),
            'Table (3)'[Date]
                >= DATE ( YEAR ( MAX ( 'Table (3)'[Date] ) ), MONTH ( MAX ( 'Table (3)'[Date] ) ), 1 )
                && 'Table (3)'[Date] <= MAX ( 'Table (3)'[Date] )
        )
    )
RETURN
    _totalsum / DAY ( MAX ( 'Table (3)'[Date] ) )

Output:-

 

Thanks,

Samarth

PBINewbie920's avatar
PBINewbie920
Helper I
4 years ago

Actually i figured it out due to help from another post! For anyone else needing this in the future: 

VAR _totalsum =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date]
>= DATE ( YEAR ( MAX ( 'Table'[Date] ) ), MONTH ( MAX ( 'Table'[Date] ) ), 1 )
&& 'Table'[Date] <= MAX ( 'Table'[Date] ) && 'Table'[Location] = MAX ( 'Table'[Location] )
)
)