Forum Discussion

Mohanad-Mustafa's avatar
Mohanad-Mustafa
Advocate III
4 years ago

Dax For Calculating Sales Projection

Hello Team Awesome,

 

I hope you all are doing great.

 

Need your help with Dax code please.

 

I need to calculate YTD sales then divide it by YTD working days in a calendar year then multiplies it by 250 which is the number of total working days in a calendar year.

 

My sales table has a full calendar column and I also have a separate calendar table.

 

Calendar year starts from January and ends in December.

 

For example, lets say my YTD sales is 100,000 , if I divided it by 163 (working days in 2022 so far) and then multiples it by 250.

That will give us a projected sales of 153,374 approxmiately.

 

Any help is highly appreciated.

 

Thanks a lot

 

Mohanad 

Need Help

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    That seems straightforward if you use  a proper calendar table (where you can also mark the actual working days for each year).  What have you tried and where are you stuck?

    • Mohanad-Mustafa's avatar
      Mohanad-Mustafa
      Advocate III
      4 years ago

      I have tried to calculate YTD sales as a measure and then dividing it over the working days which I tried to calculate but I was only able to get it in a form of true or false,  and then multiplying it by 250, that didn't work, the measure doesnt return result and throw an error, can you suggest a solution please or perhaps if someone already answer similar question, if you could send me the link of the solution? Thanks 

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    Hi Mohanad-Mustafa 

     

    Do you have a column in the calendar table which marks whether a date is a working day or not? If you have that column, it is simple to deal with this calculation. 

     

    Another question is that the YTD is to be calculated until which date? Until today or until any date you may select from a filter or slicer?

     

    It would be more helpful if you can post some dummy data to show the tables you have. 

     

    Best Regards,
    Community Support Team _ Jing

    • Mohanad-Mustafa's avatar
      Mohanad-Mustafa
      Advocate III
      4 years ago

      Hi v-jingzhang,

       

      I do have a weekday/weekend column in the calendrat table but its a condition either true or false.

      YTD means sales from Jan 1st 2022 until present time today. 

       

      I am not able to share any sample data at the time being unfortuantely, any suggestions from your end is highly appreciated. Thanks