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 ($)] )
    )
MFelix's avatar
MFelix
Super User
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
        )
    )