Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I'm looking for a DAX formula that give me the following:
I have a table with nº of sales per day from 2014 to 2017
I need to have a average per day of those sales.
Next, I want to forecast to next year that average, so i know that on the 1st of May i'm probabling going to sale 110 biscoits.
Can someone help me!
Thanks
Solved! Go to Solution.
Hi @Tiago_Varela,
Based on my test, you can refer to below steps:
1.I have entered some sample data:
2.Create a calculated column.
Month-Day = FORMAT('Table1'[Date],"MMM")&"-"&FORMAT('Table1'[Date],"D")
3.Create a measure, a Card visual and a slicer. Now you could get the average result.
Measure = CALCULATE(AVERAGE(Table1[Value]),FILTER('Table1','Table1'[Month-Day]=MAX('Table1'[Month-Day])))
If you want to forecast the 2019 sales, you just need to use the measure to multiple 10%.
You can also download the PBIX file to have a view.
Regards,
Daniel He
Hi @Tiago_Varela,
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Regards,
Daniel He
Hi @Tiago_Varela,
From your description, I could not figure out which average value you want to get. If you want to get the average value a precise day of a year you could refer to below steps:
Sample data:
Create a measure and a Table visual and you could see the result:
AVG = AVERAGEA(Table1[Value])
If you just want to calculate the average value based on the Month-Day(for example 1st of May), you could refer to this measure:
Measure = CALCULATE('Table1'[AVG],FILTER('Table1',MONTH('Table1'[Date])=MONTH('Table1'[Date])&&DAY('Table1'[Date])=DAY('Table1'[Date])))
Result:
If I misunderstood you, could you please offer me more information about your data structure or share your pbix file if possible?
You can also download the PBIX file to have a view.
Regards,
Daniel He
Hi daniel,
Thanks a lot for your help and answer.
What i have is a table with rooms sold from 2014 to 2018.
I need an average per day from those 5 years.
Per exemple:
01/01/2015 - 54
02/01/2015 - 60
03/01/2015 - 50
...
28/12/2018 - 30
29/12/2018 - 20
30/12/2018 - 60
31/12/2018 - 40
and i need per day the average of those days:
01/01 - ((01/01/15 + 01/01/16 + 01/01/17 + 01/01/18)/4))
Because then i need to say that:
That average is the same at 2019 plus or minus a certain percentage
01/01/2019 = (average of ((01/01/15 + 01/01/16 + 01/01/17 + 01/01/18)/4))) * 10%
Is a way of forecasting the sales for the future.
But i cannot do a Dax measure that can help me with this.
Also cannot figure it out how i can show you my pbx file or do a print screen and add it here! Sorry
Thanks
Hi @Tiago_Varela,
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Regards,
Daniel He
Thanks
It works fine.
Hi @Tiago_Varela,
Based on my test, you can refer to below steps:
1.I have entered some sample data:
2.Create a calculated column.
Month-Day = FORMAT('Table1'[Date],"MMM")&"-"&FORMAT('Table1'[Date],"D")
3.Create a measure, a Card visual and a slicer. Now you could get the average result.
Measure = CALCULATE(AVERAGE(Table1[Value]),FILTER('Table1','Table1'[Month-Day]=MAX('Table1'[Month-Day])))
If you want to forecast the 2019 sales, you just need to use the measure to multiple 10%.
You can also download the PBIX file to have a view.
Regards,
Daniel He
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |