Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
data_emplacamento | QNT_Total | Divisao |
30/09/2021 | 1 | 102216 |
30/09/2021 | 37 | 0 |
30/09/2021 | 15 | 0 |
30/09/2021 | 5 | 0 |
30/09/2021 | 6 | 0 |
30/09/2021 | 6 | 0 |
30/09/2021 | 5 | 0 |
30/09/2021 | 12 | 100006 |
30/09/2021 | 9 | 104747 |
30/09/2021 | 6 | 100071 |
30/09/2021 | 6 | 100092 |
30/09/2021 | 6 | 100006 |
30/09/2021 | 5 | 100050 |
30/09/2021 | 5 | 100056 |
30/09/2021 | 5 | 100007 |
30/09/2021 | 4 | 100048 |
30/09/2021 | 4 | 100025 |
30/09/2021 | 4 | 100073 |
30/09/2021 | 4 | 104613 |
30/09/2021 | 4 | 105898 |
30/09/2021 | 4 | 100101 |
Thanks in advance!
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:
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:
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 🙂
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |