Calculate Days Sales dynamically

[Anonymous]

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.

[Greg_Deckler]

@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]

Hi  @Greg_Deckler ,

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

sales table :

Report Month	Batch	Qty
1/1/2020	ABC	2000
1/1/2020	XYZ	39000
2/1/2020	ABC	1700
2/1/2020	XYZ	30000

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.

		31	29	31	30	31	30	31	31	30	31	30	31	31
Batch	Report Month	Jan-19	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan-20	Qty	DS
ABC	1-Jan			275	675		225		250	475	200	275			2,000	271
ABC	1-Feb	125		100	580		200		170	190	100	375		50	1,700	301
XYZ	1-Jan	2380	1890	2730	3570	1610	1890	1260	5110	3710	2100	4760	805		39,000	FALSE
XYZ	1-Feb	2380	1750	2427	3570	1610	1890	1260	5110	3710	2100	4760	805		30,000	FALSE

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.

[Anonymous]

Hi @Greg_Deckler ,

I have created a link to the file.

Please find the file here. 

https://www.dropbox.com/scl/fi/mpuauhbptogkd1i2e8bxp/Sample.xlsx?dl=0&rlkey=mf161uxm4itlynlmvrhlknzy...