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

• ### Question

• 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 \$ 4,00 Peter XYZ 22/03/2018 \$ 3,81 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?

Tuesday, July 31, 2018 2:05 PM

• Hi Pietro Farias,

>>>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?
You can create calculated columns to achieve this. Please note that all of the DAX formula are created as calculated columns in the sample data provided by you:

```YearMonth =
YEAR ( Sales[Date] ) * 100
+ MONTH ( Sales[Date] )

AVGSALE =
AVERAGEX (
FILTER (
Sales,
Sales[Sellers] = EARLIER ( Sales[Sellers] )
&& Sales[YearMonth] = EARLIER ( Sales[YearMonth] )
),
Sales[Sale]
)

PreviousMonth =
MAXX (
FILTER (
Sales,
Sales[Sellers] = EARLIER ( Sales[Sellers] )
&& Sales[YearMonth] < EARLIER ( Sales[YearMonth] )
),
Sales[YearMonth]
)

LastMonthAvg =
CALCULATE (
MAX ( Sales[AVGSALE] ),
FILTER (
Sales,
Sales[Sellers] = EARLIER ( Sales[Sellers] )
&& Sales[YearMonth] = EARLIER ( Sales[PreviousMonth] )
)
)

variation =
Sales[AVGSALE] - Sales[LastMonthAvg]```

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Wednesday, August 1, 2018 2:52 AM

### All replies

• Somebody? :(
Tuesday, July 31, 2018 8:02 PM
• Hi Pietro Farias,

>>>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?
You can create calculated columns to achieve this. Please note that all of the DAX formula are created as calculated columns in the sample data provided by you:

```YearMonth =
YEAR ( Sales[Date] ) * 100
+ MONTH ( Sales[Date] )

AVGSALE =
AVERAGEX (
FILTER (
Sales,
Sales[Sellers] = EARLIER ( Sales[Sellers] )
&& Sales[YearMonth] = EARLIER ( Sales[YearMonth] )
),
Sales[Sale]
)

PreviousMonth =
MAXX (
FILTER (
Sales,
Sales[Sellers] = EARLIER ( Sales[Sellers] )
&& Sales[YearMonth] < EARLIER ( Sales[YearMonth] )
),
Sales[YearMonth]
)

LastMonthAvg =
CALCULATE (
MAX ( Sales[AVGSALE] ),
FILTER (
Sales,
Sales[Sellers] = EARLIER ( Sales[Sellers] )
&& Sales[YearMonth] = EARLIER ( Sales[PreviousMonth] )
)
)

variation =
Sales[AVGSALE] - Sales[LastMonthAvg]```

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Wednesday, August 1, 2018 2:52 AM