Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Measure Difficulties

Hi Team,    I have a simple table with 3 columns.    I have been tasked to create a measure. The client wants to see the Average sales per customer where the SalesDate falls in between the period...
Need Help
Tutorial Requests
  • MFelix's avatar
    MFelix
    6 years ago

    Hello @water_hydration ,

    This is related to the filter context should be after the AVERAGEX and not within AVERAGEX:

    12 Month Sales =
VAR __startcurrentmonth =
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
VAR __previous12 =
    EDATE ( __startcurrentmonth, -12 )
RETURN
    CALCULATE (
        AVERAGEX ( SalesTable, AVERAGE ( SalesTable[Sales ($)] ) ),
        FILTER (
            ALL ( SalesTable ),
            SalesTable[SalesDate] <= __startcurrentmonth
                && SalesTable[SalesDate] >= __previous12
        )
    )

MFelix's avatar
MFelix
Super User
6 years ago

Hi  Anonymous ,

 

Measures are based on context so you cannot use the columns as part of the your calculation you need to use aggregation.

 

On your case believe that you need to have the AVERAGEX that makes a calculations of a table row by row based on the individual values. Try something similar to this:

 

12 Month Sales =
VAR __startcurrentmonth =
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
VAR __previous12 =
    EDATE ( __startcurrentmonth, -12 )
RETURN
    AVERAGEX (
        FILTER (
            ALL ( SalesTable ),
            SalesTable[SalesDate] <= __startcurrentmonth
                && SalesTable[SalesDate] >= __previous12
        ),
        SUM ( SalesTable[Sales ($)] )
    )
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi MFelix 

 

Wow! That was a super fast response - thank you. 

 

Unfortunately the formula doesn't work. It's close. I changed the sum to average at the end which helped but the main issue is that it is ignoring the date filters. It brings back the average for the full dataset rather than only the months we want to include.

 

Any expertise or help you could offer would be extremely appreciated. 

 

Thanks

 

W

 

12 Month Sales =
VAR __startcurrentmonth =
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
VAR __previous12 =
    EDATE ( __startcurrentmonth, -12 )
RETURN
    AVERAGEX (
        FILTER (
            ALL ( SalesTable ),
            SalesTable[SalesDate] <= __startcurrentmonth
                && SalesTable[SalesDate] >= __previous12
        ),
        Average ( SalesTable[Sales ($)] )
    )