Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Calculate Days Sales dynamically

Hi all,

 

I have posted about this before and got some great responses--but my problem is complicated and no matter what I try it is not working out.

 

(I hope it is not against community guidelines to post again, I had asked this more complicated question in the comments of my more simple question after it had been marked as solved so I dont think anyone went to look at it) 

 

 I have the above Sales Table. 

 

And a historical data table. There is more data for Month 2/1/2020 as well. This is just the snapshot. I am not getting the otion to attach the excel file as I wanted to do so. 

Below is how I calculate DS in excel - >

 

 

I need to find an alternative to the below formula for DS, the below formula is in excel only for Report Month -01:-

 

For Month -02-  the formula changes to:-

So basically what it does is , it compares Qty with last 12 months of data. 

So for 01-01-2020 -> it takes data from S3 to G3 i.e. Dec-Jan19.

For 01-02-2020 - > Data from T3 to H3 ie. Jan20, all Dec-Feb19.

and also it multiplies the value by number of days in month considered. (If you can see the formula and find $R$1-> no of days in DEc-19)

 

I have all formulas for running totals, but that does not help in any way. 

The end result should be DS calculated for every Batch, for every month considering 12 months summation data.

 

In excel I pivot the historical data and put formulas in columns after that. 

I was thinking if i could get the DS calculated in Sales table will also do,like below.

 

If anybody can help please. I am stuck on this from many days. 

Also I am trying to attach the excel file for reference, but ther is no option for attaching.

Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Anonymous - I am not clear on this, generally the way to attach an Excel file is to share it on OneDrive, Box, etc. and post a link to it here. I can't make heads or tails of your Excel formula because there is no reference for what R or S is, etc. The very best thing to do is to post your sample data as text here using the table input in the ribbon when posting. 

     

    Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi  Greg_Deckler ,

       

      I tried to paste the data here, but i am unable to attach excel file still..

      sales table :

      Report MonthBatchQty
      1/1/2020ABC2000
      1/1/2020XYZ39000
      2/1/2020ABC1700
      2/1/2020XYZ30000

       

      I pivot the Historic table as below:-(the historical data is huge)

      Following is the excel pivot table  which i create and then add Qty and DS columns at the end.

        31293130313031313031303131  
      BatchReport MonthJan-19FebMarAprMayJunJulAugSepOctNovDecJan-20QtyDS
      ABC1-Jan  275675 225 250475200275  2,000271
      ABC1-Feb125 100580 200 170190100375 501,700301
      XYZ1-Jan23801890273035701610189012605110371021004760805 39,000FALSE
      XYZ1-Feb23801750242735701610189012605110371021004760805 30,000FALSE

       

      Qty Column is just a sum if from Sales Table. And 30& 31 is number of days of that month.

      I have tried to make the formula understable :- 

      IFERROR(IF(Qty<=Dec,Qty/Dec*31,IF(Qty<=(Dec+Nov),(Qty-Dec)/Nov*30+31,IF(Qty<=(Dec+Nov+Oct),(Qty-Dec-Nov)/Oct*31+31+30,IF(Qty<=(Dec+Nov+Oct+Sep),(Qty-Dec-Nov-Oct)/Sep*30+31+30+31,IF(Qty<=(Dec+Nov+Oct+Sep+Aug),(Qty-Dec-Nov-Oct-Sep)/Aug*31+31+30+31+30,IF(Qty<=(Dec+Nov+Oct+Sep+Aug+Jul),(Qty-Dec-Nov-Oct-Sep-Aug)/Jul*31+31+30+31+30+31,IF(Qty<=(Dec+Nov+Oct+Sep+Aug+Jul+Jun),(Qty-Dec-Nov-Oct-Sep-Aug-Jul)/Jun*30+31+30+31+30+31+31,IF(Qty<=(Dec+Nov+Oct+Sep+Aug+Jul+Jun+May),(Qty-Dec-Nov-Oct-Sep-Aug-Jul-Jun)/May*31+31+30+31+30+31+31+30,IF(Qty<=(Dec+Nov+Oct+Sep+Aug+Jul+Jun+May+Apr),(Qty-Dec-Nov-Oct-Sep-Aug-Jul-Jun-May)/Apr*30+31+30+31+30+31+31+30+31,IF(Qty<=(Dec+Nov+Oct+Sep+Aug+Jul+Jun+May+Apr+Mar),(Qty-Dec-Nov-Oct-Sep-Aug-Jul-Jun-May-Apr)/Mar*31+31+30+31+30+31+31+30+31+30,IF(Qty<=(Dec+Nov+Oct+Sep+Aug+Jul+Jun+May+Apr+Mar+Feb),(Qty-Dec-Nov-Oct-Sep-Aug-Jul-Jun-May-Apr-Mar)/Feb*29+31+30+31+30+31+31+30+31+30+31,IF(Qty<=(Dec+Nov+Oct+Sep+Aug+Jul+Jun+May+Apr+Mar+Feb+Jan-19),(Qty-Dec-Nov-Oct-Sep-Aug-Jul-Jun-May-Apr-Mar-Feb)/Jan-19*31+31+30+31+30+31+31+30+31+30+31+29)))))))))))),0)

        

      Same formula will be for Report month-2, the months will be from Jan-20 to Feb-19.