Change in the value of the current period with the last non-blank period

[PietroFarias]

Some time ago, I had to return the variation from the sale of the current day to the last sale made, in which it would not necessarily be the previous day always or before the previous day. I need help now.

Sellers	Customer	Date	Sale
Beth	WWW	22/04/2017	$ 3,97
Beth	WWW	07/05/2017	$ 3,12
Beth	WWW	07/01/2018	$ 1,41
Paul	PKZ	14/01/2017	$ 3,25
Paul	PKZ	02/04/2017	$ 2,97
Paul	PKZ	16/02/2018	$ 3,47
Peter	XYZ	08/01/2018	$ 3,45
Peter	XYZ	10/03/2018	$ 3,81
Peter	XYZ	22/03/2018	$ 4,00
Peter	XYZ	23/05/2018	$ 1,29

I need to return the variation from the average sale of the month to the last month. Peter, for example, made a sale in May. And before it did two sales in March. How do I return the variation of these two periods in the DAX?

[v-shex-msft]

HI @PietroFarias,

>>How do I return the variation of these two periods in the DAX?

You can try to use below measures to get average values from different period.

Avg of Previous Period= 
VAR currDate =
    MAX ( Table5[Date] )
RETURN
    CALCULATE (
        AVERAGE ( Table5[Sale] ),
        FILTER (
            ALLSELECTED ( Table5 ),
            FORMAT ( [Date], "mm/yyyy" )
                = FORMAT ( DATE ( YEAR ( currDate ), MONTH ( currDate ) - 1, 1 ), "mm/yyyy" )
        ),
        VALUES ( Table5[Sellers] ),
        VALUES ( Table5[Customer] )
    )

Avg of Current Period= 
VAR currDate =
    MAX ( Table5[Date] )
RETURN
    CALCULATE (
        AVERAGE ( Table5[Sale] ),
        FILTER (
            ALLSELECTED ( Table5 ),
            FORMAT ( [Date], "mm/yyyy" ) = FORMAT ( currDate, "mm/yyyy" )
        ),
        VALUES ( Table5[Sellers] ),
        VALUES ( Table5[Customer] )
    )

Regards,

Xiaoxin Sheng

[PietroFarias · Accepted solution]

Thanks Sheng.

But, your formula returned ever the previous month and not the last period in which the sale took place, sometimes it was many months ago.

So, i did that:

I created a Calculated Column with LastPeriod

= VAR LstPeriod =
MAXX (
    FILTER (
            Sales;
        Sales[Date] <= EOMONTH(EARLIER(Sales[Date]);-1)
            && Sales[Saller] = EARLIER ( [Saller] )
            && Sales[Customer] = EARLIER( [Customer])
    );
    Sales[Date]
)
RETURN
    FORMAT(YEAR(LstPeriod);"0000") & "-" & FORMAT(MONTH(LstPeriod);"00")

And another calculated column with the AVERAGE of this last period.

=CALCULATE(
		Sales[LastPeriod];
		FILTER(
			Sales;
			[CurrentPeriod] = EARLIER([LastPeriod])
				&& [Customer] = EARLIER([Customer])
				&& [Saller] = EARLIER([Saller])
		)
)

And ready. Create a measure with variation.

MedRejuste:=DIVIDE(
	AVERAGE([Sale]) - AVERAGE(AvgLastPeriod);
	AVERAGE(AvgLastPeriod);
	BLANK()
)

If you see something that can improve, it will be helpful.

[PietroFarias]

Somebody?