Measure Difficulties

[Anonymous]

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 Name	Sales ($)	SalesDate
West Ham	325,031	01/02/2020
West Ham	170,353	01/01/2019
West Ham	844,280	01/01/2019
West Ham	298,361	01/03/2020
West Ham	371,474	01/01/2020
West Ham	599,903	01/02/2019
West Ham	364,402	01/12/2019
West Ham	574,705	01/11/2019
West Ham	796,911	01/10/2019

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

Thanks

[amitchandak]

You can try like

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

[Anonymous]

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.  

[MFelix]

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 · Accepted solution]

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]

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

[MFelix]

Hi @Anonymous ,

When you use variable you need to start the calculation with a return, basically what this measure is calculating is the AVERAGE value for each row of data within the sales table but to the calculation (CALCULATE) it apply a filter that is the second part of the CALCULATE so it only runs the AVERAGEX for all values contained within the dates considered on the dates you define on the variables.

The ALL part of the solution was a mistake from my part since I have taken out the filter to the calculate if you place the ALL it will remove all filter context and return the same value for all lines in your case client. The ALL is used when you wan to make percentage over total for example.

[Anonymous]

Thank you so much @MFelix 

[Anonymous]

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