Forum Discussion

Krijgersss's avatar
Krijgersss
Icon for Helper II rankHelper II
2 years ago
Solved

need help with a future calculation

i need some help to make a future calculation. for example in February i sold 28.000 in 28 days so for march the future calculation must say 31.000 because march has 31 days so 1000 x 31. this is a r...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Krijgersss ,

     

    According to your statement, I think you want to forecast the sales based on last month sales.

    I suggest you to try my workaround.

    Data model:

    Measure:

    Days Count = CALCULATE(DISTINCTCOUNT('Calendar'[Date]))
    Forecast Sales based on last month = 
CALCULATE(SUM('Table'[Sales]),PREVIOUSMONTH('Calendar'[Date])) / CALCULATE(DISTINCTCOUNT('Calendar'[Date]),PREVIOUSMONTH('Calendar'[Date])) * [Days Count]

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

     

ValtteriN's avatar
ValtteriN
Icon for Community Champion rankCommunity Champion
2 years ago

Hi,

Here is one way to do this:
Data:

 

Dax:

Act / forecast =
IF(MAX('Calendar'[Date])>TODAY(),

CALCULATE(
SUM('Table (31)'[Value]),DATEADD('Calendar'[Date],-1,MONTH)),
SUM('Table (31)'[Value]))




End result:

 

Here we test for if date in fact is larger than today -> future value. Then we calculate a forecast. Here it is based on the value of previous month. So April and May are forecasts here. Here the logic is to simply take the previous months value, but the logic can be whatever. E.g. here the value is lastmonth's value times 2.

Act / forecast *2 =
IF(MAX('Calendar'[Date])>TODAY(),

CALCULATE(
SUM('Table (31)'[Value]),DATEADD('Calendar'[Date],-1,MONTH))*2,
SUM('Table (31)'[Value]))

 


I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/



  • Krijgersss's avatar
    Krijgersss
    Icon for Helper II rankHelper II
    2 years ago

    basicly i want the revenu of last month per day calculated on next month so for example february its 29,000 let say 1,000 per day than march will be 31,000 because february is 29days a 1,000 perday so my prognose/ future calculation will be 2 more days +1,000 is 31,000

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Krijgersss ,

       

      According to your statement, I think you want to forecast the sales based on last month sales.

      I suggest you to try my workaround.

      Data model:

      Measure:

      Days Count = CALCULATE(DISTINCTCOUNT('Calendar'[Date]))
      Forecast Sales based on last month = 
CALCULATE(SUM('Table'[Sales]),PREVIOUSMONTH('Calendar'[Date])) / CALCULATE(DISTINCTCOUNT('Calendar'[Date]),PREVIOUSMONTH('Calendar'[Date])) * [Days Count]

      Result is as below.

       

      Best Regards,
      Rico Zhou

       

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