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 between the start of today's current month and the previous 12 months.

 

This is my attempt: 

 

12 Month Sales =

VAR __startcurrentmonth = Date(Year(Today()), Month(Today()),1)

VAR __previous12 = EDATE(__startcurrentmonth,-12)

if(and(SalesDate<=VAR __startcurrentmonth, SalesDate>=VAR __previous12), average(Sales ($)), "")

 

Cust NameSales ($)SalesDate
West Ham         325,03101/02/2020
West Ham         170,35301/01/2019
West Ham         844,28001/01/2019
West Ham         298,36101/03/2020
West Ham         371,47401/01/2020
West Ham         599,90301/02/2019
West Ham         364,40201/12/2019
West Ham         574,70501/11/2019
West Ham         796,91101/10/2019

 

I am running into terrible issues though. Can anyone help me out? 

 

Thanks

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

8 Replies

  • 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 ($)] )
    )

       

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Thanks so much MFelix !

         

        Can you explain the last bit from the return? 

         

        I am a little confused as to how it works. The only bit I had to eliminate was the 'All' from the function as it kept returning the same number per customer. 

         

        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.