Forum Discussion

visheshjain's avatar
visheshjain
Icon for Impactful Individual rankImpactful Individual
3 years ago
Solved

Bifurcation over irregular months

Hello everyone,

 

Apologies, that this might be a long question, but I have tried to give my methodology, for you to get started.

If you have a better approach, I am all ears, I do not want to create a separate table and minimize the use of calculated columns.

 

I have a table with the following data, where the Employee Sales are over a specified period of time and I want to bifurcate them over a calendar month, based on the days of that calendar month.

 

Month Employee Name Sales Date From Date To
Aug'22 X 270000 01-Aug-22 31-Aug-22
Sep-Oct'22 X 600000 01-Sep-22 15-Oct-22
OCT - OCT X 700000 16-Oct-22 24-Oct-22
Oct-Nov X 300000 25-Oct-22 06-Nov-22
Nov-Dec X 500000 07-Nov-22 31-Dec-22
Aug'22 Y 200000 01-Aug-22 31-Aug-22
Sep-Oct'22 Y 500000 01-Sep-22 15-Oct-22
OCT - OCT Y 600000 16-Oct-22 24-Oct-22
Oct-Nov Y 200000 25-Oct-22 06-Nov-22
Nov-Dec Y 400000 07-Nov-22 31-Dec-22

 

So for example for Employee X, the Sales of 300,000, for the period of Oct-Nov, which consists of 13 days, will be divided into 2 parts:

25 Oct 2022 to 31 Oct 2022 which is 7 Days

1 Nov 2022 to 6 Nov 2022 which is 6 Days

So the Net sales for 1 Nov 2022 to 6 Nov 2022 will be (300000*6)/13 = 138461.5

 

In the same way for Employee X, the Sales of 500,000, for the period of Nov - Dec, which consists of 55 days, will be divided into 2 parts:

7 Nov 2022 to 30 Nov 2022 which is 24 Days

1 Dec 2022 to 31 Dec 2022 which is 31 Days

So the Net sales for 7 Nov 2022 to 30 Nov 2022 will be (500000*24)/55 = 281818.2

 

The Final sales for Employee X for Nov 2022 will be 138461.5 + 281818.2 = 356643.4

 

This is the final output and I am looking for a measure Final Sales

Employee Name Month Final Sales
X Aug-22 270000
X Sep-22 400000
X Oct-22 1061538
X Nov-22 356643.4
X Dec-22 281818.2
Y Aug-22 200000
Y Sep-22 333333.3
Y Oct-22 874359
Y Nov-22 266853.1
Y Dec-22 225454.5

 

Thank you for reading this!

 

Vishesh Jain

Need Help
Show and Tell

2 Replies

  • visheshjain's avatar
    visheshjain
    Icon for Impactful Individual rankImpactful Individual
    3 years ago

    Here is an intermediary table showing the bifurcation of other months as well:

     

    Employee Name Month Date From Date To Sales Duration Days In current Month Sales Net Sales Final Sales
    X Aug-22 01-Aug-22 31-Aug-22 31 31 270000 270000 270000
    X Sep-22 01-Sep-22 30-Sep-22 45 30 600000 400000 400000
    X Oct-22 01-Oct-22 15-Oct-22 45 15 600000 200000  
    X Oct-22 16-Oct-22 24-Oct-22 9 9 700000 700000  
    X Oct-22 25-Oct-22 31-Oct-22 13 7 300000 161538.5 1061538.5
    X Nov-22 01-Nov-22 06-Nov-22 13 6 300000 138461.5  
    X Nov-22 07-Nov-22 30-Nov-22 55 24 500000 218181.8 356643.36
    X Dec-22 01-Dec-22 31-Dec-22 55 31 500000 281818.2 281818.18
    Y Aug-22 01-Aug-22 31-Aug-22 31 31 200000 200000 200000
    Y Sep-22 01-Sep-22 30-Sep-22 45 30 500000 333333.3 333333.33
    Y Oct-22 01-Oct-22 15-Oct-22 45 15 500000 166666.7  
    Y Oct-22 16-Oct-22 24-Oct-22 9 9 600000 600000  
    Y Oct-22 25-Oct-22 31-Oct-22 13 7 200000 107692.3 874358.97
    Y Nov-22 01-Nov-22 06-Nov-22 13 6 200000 92307.69  
    Y Nov-22 07-Nov-22 30-Nov-22 55 24 400000 174545.5 266853.15
    Y Dec-22 01-Dec-22 31-Dec-22 55 31 400000 225454.5 225454.55