Forum Discussion

Oomsen's avatar
Oomsen
Helper III
6 years ago
Solved

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....
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    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

amitchandak's avatar
amitchandak
Super User
6 years ago

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))/6

 To 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.

Oomsen's avatar
Oomsen
Helper III
6 years ago

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.
  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    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

    • Oomsen's avatar
      Oomsen
      Helper III
      6 years ago

      v-lili6-msft amitchandak  it works. 

      For future months it is using 0 for calculating the average instead of the amount in the measure.

      Example:

      jan feb mrt apr may  jun  jul  aug  sep  oct  nov  dec

      10  10  20   30   40    50   27  25    23

      Expected:

      jan feb mrt apr may  jun  jul  aug  sep  oct  nov  dec

      10  10  20   30   40    50   27  31    33    

      • v-lili6-msft's avatar
        v-lili6-msft
        Community Support
        6 years ago

        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