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

amitchandak's avatar
amitchandak
Super User
6 years ago

You can try like

Rolling 12 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],startofmonth(Sales[Sales Date]),-12,MONTH))

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi amitchandak 

     

    Thanks so much for your response. 

     

    I don't have a 'Date' table. 

    DATESINPERIOD('Date'[Date]

     

    So I changed it to DATESINPERIOD('Sales'[Date]

     

    However, the overall formula just returns the value for the first date.