Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MichelDias
Frequent Visitor

Expected sales based on Average of last 3 months (same period)

Hi! I receive monthly a list of sales (with days and amount), but as I don't have the actual month sales I wanted to somehow calculate what would be the expected sales for the actual month, based on the average sales of the same period in the last 3 months.

 

For example, today is October 15th so I wanted to check the average of sales from days 1 to 14 in the months of July, August and September.

 

I have found a not so precise way to do this, creating a calculated True/False colum inside my sales table based on the date colum (data_emplacamento) as bellow:

 

Usar Comparativo =
VAR UltimoEmplacamento =
    MAX ( '#EMPLAC'[data_emplacamento] ) 
VAR DiaReferencia =
    DAY ( TODAY () - 1 )
VAR Valida =
    IF (
        AND (
            '#EMPLAC'[data_emplacamento] >= UltimoEmplacamento - 92,
            DAY ( '#EMPLAC'[data_emplacamento] ) <= DiaReferencia
        ),
        TRUE,
        FALSE
    )
RETURN
    Valida
 
Next to it I created a measure that sums the sales (column QNT_Total) true flagged lines and divide by 3:
#Sales Preview = CALCULATE(SUM('#EMPLAC'[QNT_Total]),'#EMPLAC'[Usar Comparativo]=TRUE)/3
 
I am afraid this formula won't work correctly all the time, but I'm still struggling to find a more precise way to calculate these dynamic dates, so any help will be much appreciated.
 
I will try to attach a sample xlsx file with data, but here is a simplified structure of how the data is structured (the date column data_emplacamento is already formatted as date in pt-BR formatting location):
 
data_emplacamentoQNT_TotalDivisao
30/09/20211102216
30/09/2021370
30/09/2021150
30/09/202150
30/09/202160
30/09/202160
30/09/202150
30/09/202112100006
30/09/20219104747
30/09/20216100071
30/09/20216100092
30/09/20216100006
30/09/20215100050
30/09/20215100056
30/09/20215100007
30/09/20214100048
30/09/20214100025
30/09/20214100073
30/09/20214104613
30/09/20214105898
30/09/20214100101

 

Thanks in advance!

6 REPLIES 6
MichelDias
Frequent Visitor

Actually, with a hand from SQLBI channel on YT, I found a good DAX logic that fitted better without the need of a personalized column, using variables:

#Veiculos Previsao Teste 01 =
VAR DiaBase =
DAY ( TODAY () - 1 ) --get the day number
VAR DataFinalTabela =
MAX ( '#EMPLAC'[data_emplacamento] ) --last date to be used
VAR DataInicialTabela =
DATE ( YEAR ( EDATE ( DataFinalTabela, -2 ) ), MONTH ( EDATE ( DataFinalTabela, -2 ) ), 1 ) --first day of 2 months before
VAR PeriodoCalculo =
FILTER (
'#EMPLAC',
'#EMPLAC'[data_emplacamento] >= DataInicialTabela
&& '#EMPLAC'[data_emplacamento] <= DataFinalTabela
&& DAY ( '#EMPLAC'[data_emplacamento] ) <= DiaBase --filter only days with number lower or equal the one I need
)
RETURN
CALCULATE ( SUM ( '#EMPLAC'[QNT_Total] ), PeriodoCalculo ) / 3 --use the period variable as filter context and divide by 3 to get the average
 
This formula also works if I use a calendar table in the variable with the filter parameters. Any other options are welcome.
v-stephen-msft
Community Support
Community Support

Hi @MichelDias ,

 

Try DATESINPERIOD function

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

Then Average = DIVIDE( [Rolling 3], 3)

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Stephen, thanks for your response, but actually it won't work.

The DATESINPERIOD function will retrieve all the dates within the selected period, but I need to exclude the days that whose day number are higher or equal than the actual day. Also, as I have more than one row per total amount, I would need to use the SUM inspite of the AVERAGE inside the CALCULATE function (this is not a problem once I simply need to divide by 3 the result).

I tried this formula with the referenced dates and amount columns and it gave me the total including the dates I dont want to have evaluated:  

Rolling 3 = CALCULATE(SUM('#EMPLAC'[QNT_Total]),DATESINPERIOD('#EMPLAC'[data_emplacamento],MAX('#EMPLAC'[data_emplacamento]),-3,MONTH))/3
lbendlin
Super User
Super User

Your approach is as good as any of the many other approaches.  All of them fall short of acknowledging the weekday cadence differences. This is especially problematic early in the month.  For example a prior month may start on a saturday, and another on a monday.  Comparing sales upto day 3 of each month will be entirely useless.

 

The longer your period the more it smoothes out.  Let's say Year over Year To Date comparisons are "good enough"  after the first month of the year. They are not accurate, but they are "good enough".

 

Additional complications arise from variable holidays that may fall on different weekdays each year, and may impact sales numbers differently. 

Thanks for pointing this @lbendlin , that's exactly what is motivating me to get the last 3 months (even the last 6 months or more, once I find a way to solve my problem).

I know that weekends could make my measure having low precision mainly in the first day of each month, but also as the average days of a month are 30/31 and the weeks always have 7 days, looking at the last 3 months will gave me a measure that have a higher probability of having 2 working/1 not working days. If you get the actual month, for example, and look at day 1, you can see that this month it was on a Friday, las month it was on a Wednesday and on August it was on a Sunday. This offset of days is mathematically explained by the 2 or 3 days of each month variation (excluding only February) on the Weekdays, the average offset of days considering 3 months and the next 40 years is 7.31 - using 6 months it is 14.62.

Other reason to use the same days of the month, is that there is a seasonal behaviour of an increase of sales based on pay date and here in Brazil the more common payment practices are to pay salaries once or twice per month in fixed days.

Anyway, thanks for the comment!

I'm pretty sure there is a ML model out there somewhere that takes all these factors into account. And if there isn't one yet then this would be an incentive to create it 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.