Forum Discussion
Salesforecast
Hello, i'm looking for a measure to forecast my sales.
I would like to sum the actual sales by month until the current month.
For futher months i would like to have a average of the last 6 months.
Example:
jan feb mrt apr may jun jul aug
100 50 20 10 10 30
july would be (100+50+20+10+20+30)/6 = 38
aug would be (50+20+10+20+30+38)/6 = 28
hi Oomsen
I think it could not achieve in dax in power bi, this since it will calculate by its own value, it is very difficult to achieve.
Regards,
Lin
5 Replies
Oomsen , use rolling 6 and divide by 6
example
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-6,MONTH)) /6 or Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-6,MONTH))/6To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.amitchandak , thanks for your respons.
My current measure is
fomzet = IF(MONTH(MIN(Dimdate[MaandVanJaar]))<=MONTH(TODAY()),CALCULATE(SUM(Transacties[amountdc])*-1,'Grootboekclassificaties'[parent_description]IN{"Opbrengsten"},ALL('Grootboekclassificaties'[parent_code_attr])),CALCULATE(SUM(Transacties[amountdc])*-1,'Grootboekclassificaties'[parent_description]IN{"Opbrengsten"},ALL('Grootboekclassificaties'[parent_code_attr]),DATESINPERIOD(Dimdate[Datum],MAX(Dimdate[Datum]),-6,MONTH))/6)some parts are in dutch, sorry for that.the current result is that the past and current month give the correct value but the future months are blank.hi Oomsen
Please make sure use Date field from Dimdate in the visual.
If you still have the problem, please share your sample pbix file for us have a test
Regards,
Lin
