Pro rata based on duration according to month

[Flo_19]

Hello everybody,

For my project, in want to represent a value on a pro rata basis, according to its duration. Let me explain :

If a task lasts 1.5 month like this : 

I want to add +0.67 (1/1,5 = 0.67) in January and +0.33 (0.5/1.5 = 0.33) in Feburary 

For doing this, I have the following columns : 

ARRET : count of the value I want to show

DATE DEBUT : Start date of the task

DATE FIN : End date of the task

DU : Duration of the task

Mois : Beginning month of the task

I want to know how to proceed to reach my goal. 

At the end of the work, I want to have something like this : 

I want to make one measure for each color (they are not calculated from the same column).

Thank you for your help.

[Ashish_Mathur]

Hi,

What do the colours on the column chart represent?  Also, data in a format that can be pasted in an MS Excel file.

[Flo_19]

Hello @Ashish_Mathur,

The colours are the type of my value, let me show you a sample of my dataset.

YEAR	START DATE	END DATE	DURATION	FIRST TYPE	SECOND TYPE
2022	01/01/2022	15/02/2022	45	A	/
2023	15/03/2023	18/05/2023	64	B	D
2025	07/01/2025	06/02/2025	30	A	/
2021	24/08/2021	12/12/2021	110	C	D
2025	09/03/2025	27/03/2025	18	B	/
2027	12/04/2027	16/05/2027	34	A	/
2024	13/03/2024	20/10/2024	221	A	/
2023	12/12/2023	16/01/2024	35	C	/
2021	09/06/2021	16/08/2021	68	B	/
2022	30/10/2022	13/12/2022	44	B	/
2024	27/02/2024	04/04/2024	37	B	/
2024	07/01/2024	15/03/2024	68	C	/
2025	17/07/2025	20/08/2025	34	C	/
2026	30/01/2026	22/07/2026	173	A	/
2023	10/11/2023	04/02/2024	86	C	/
2023	03/05/2023	25/05/2023	22	B	/
2027	04/08/2027	15/09/2027	42	C	/
2022	27/06/2022	30/07/2022	33	A	D
2022	21/02/2022	13/05/2022	81	C	D
2023	24/03/2023	10/07/2023	108	B	/
2024	09/09/2024	12/10/2024	33	B	/
2026	17/11/2026	13/12/2026	26	B	/
2024	02/01/2024	16/05/2024	135	A	/
2023	12/07/2023	15/02/2024	218	A	/
2023	18/05/2023	30/07/2023	73	B	/
2022	15/04/2022	18/05/2022	33	C	/
2025	13/04/2025	22/07/2025	100	C	D
2022	17/08/2022	22/09/2022	36	A	/
2025	15/09/2025	19/10/2025	34	A	D

I have for informations all these columns, the value I want to show on my visual corresponds to stops on site. I would like to know the proportion of the duration of my stop over the month, compared to the total duration of this stop. 

If we take the first line of my sample, the stop begin 01/01/2022 and stop 15/02/2022. We have 30 days of stop in January and 15 days in February. So I want to have 30/45 for January (duration on January / Total duration) and 15/45 for February.

After I want to make the sum of all these results for each month, for having the pro rata basis occupation of my stops per month. 

All stops have one or two type : 

 - Column FIRST TYPE is A,B or C 

 - Columns SECOND TYPE is D or nothing

If a stop has in FIRST TYPE "A" and in SECOND TYPE "D", I want to count my stop for A and for D (one stop for A and one stop for D, I need to dissociate both)

At the end, I want to have this kind of visual

(The values of the columns are wrong here, I made my visual without the prorata basis. I we take the first line of my sample, begin 01/01/2022 and stop 15/02/2022, it will count 1 for January and 1 for february, where I want 0.67 for January and 0.33 for February).

Thank you for your interest.

[Ashish_Mathur]

I still do not understand your requirement.  To make things simple, based on the sample table that you have shared, show the expected result in a simple Table format.  Once that is done, we can create any visual we want. 

[Flo_19]

Hello @Ashish_Mathur ,

I finally find the solution for my problem !

Thanks you a lot for your proposition of helping me, but it's not necessary anymore. 

Sincerely.

Florian.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Flo_19]

Hello @lbendlin,

Here is a sample of my dataset.

YEAR	START DATE	END DATE	DURATION	FIRST TYPE	SECOND TYPE
2022	01/01/2022	15/02/2022	45	A	/
2023	15/03/2023	18/05/2023	64	B	D
2025	07/01/2025	06/02/2025	30	A	/
2021	24/08/2021	12/12/2021	110	C	D
2025	09/03/2025	27/03/2025	18	B	/
2027	12/04/2027	16/05/2027	34	A	/
2024	13/03/2024	20/10/2024	221	A	/
2023	12/12/2023	16/01/2024	35	C	/
2021	09/06/2021	16/08/2021	68	B	/
2022	30/10/2022	13/12/2022	44	B	/
2024	27/02/2024	04/04/2024	37	B	/
2024	07/01/2024	15/03/2024	68	C	/
2025	17/07/2025	20/08/2025	34	C	/
2026	30/01/2026	22/07/2026	173	A	/
2023	10/11/2023	04/02/2024	86	C	/
2023	03/05/2023	25/05/2023	22	B	/
2027	04/08/2027	15/09/2027	42	C	/
2022	27/06/2022	30/07/2022	33	A	D
2022	21/02/2022	13/05/2022	81	C	D
2023	24/03/2023	10/07/2023	108	B	/
2024	09/09/2024	12/10/2024	33	B	/
2026	17/11/2026	13/12/2026	26	B	/
2024	02/01/2024	16/05/2024	135	A	/
2023	12/07/2023	15/02/2024	218	A	/
2023	18/05/2023	30/07/2023	73	B	/
2022	15/04/2022	18/05/2022	33	C	/
2025	13/04/2025	22/07/2025	100	C	D
2022	17/08/2022	22/09/2022	36	A	/
2025	15/09/2025	19/10/2025	34	A	D

I have for informations all these columns, the value I want to show on my visual corresponds to stops on site. I would like to know the proportion of the duration of my stop over the month, compared to the total duration of this stop. 

If we take the first line of my sample, the stop begin 01/01/2022 and stop 15/02/2022. We have 30 days of stop in January and 15 days in February. So I want to have 30/45 for January (duration on January / Total duration) and 15/45 for February.

After I want to make the sum of all these results for each month, for having the pro rata basis occupation of my stops per month. 

All stops have one or two type : 

 - Column FIRST TYPE is A,B or C 

 - Columns SECOND TYPE is D or nothing

If a stop has in FIRST TYPE "A" and in SECOND TYPE "D", I want to count my stop for A and for D (one stop for A and one stop for D, I need to dissociate both)

At the end, I want to have this kind of visual

(The values of the columns are wrong here, I made my visual without the prorata basis. I we take the first line of my sample, begin 01/01/2022 and stop 15/02/2022, it will count 1 for January and 1 for february, where I want 0.67 for January and 0.33 for February).

Thank you for your interest.

[lbendlin]

First step is to correct your source data to bring it into a usable form

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bVNbEoMwCLyL350JkJf5bO0tnN7/GgWFhKgz+gGzbBZY9n0BDPwREC2vBXMAsuDNf1h+r/1IR0lHwawBsgUf/r8HBqryZE5BUZ488VAKsEoahYcCkgXb4Gn6lpRSdcFn6KEASdJVeIrqqbPmqKVJeCAgWPD2PKeEqDxomK1jRE/pmotrYOiJRn/MMCopTRhphYye1Z8NpAnTZ5h0F/GuBxlT+3xA9eQJw3pOniIY0oIy986a0XoXPfTQe3SrpnzZu2Jsp1U1Nwu2qfdiIxFx1Xvs3Duhsx7awzR5Q/wzfGg8Fz1NJST1GNznzDPEPp++rzLzkN+FeezmnyGh384F488lPmuW0tR7Xy+9Dz+nvndyJhjzEW+sViqY9nzLzUqx6XzsTpnn9wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"START DATE" = _t, #"END DATE" = _t, #"FIRST TYPE" = _t, #"SECOND TYPE" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"START DATE", "END DATE"}, "Attribute", "Value"),
    #"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Other Columns",{"START DATE", "END DATE", "Value"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Value] <> "/"))
in
    #"Filtered Rows"

Then you can expand the dates between Start and End

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bVNbEoMwCLyL350JkJf5bO0tnN7/GgWFhKgz+gGzbBZY9n0BDPwREC2vBXMAsuDNf1h+r/1IR0lHwawBsgUf/r8HBqryZE5BUZ488VAKsEoahYcCkgXb4Gn6lpRSdcFn6KEASdJVeIrqqbPmqKVJeCAgWPD2PKeEqDxomK1jRE/pmotrYOiJRn/MMCopTRhphYye1Z8NpAnTZ5h0F/GuBxlT+3xA9eQJw3pOniIY0oIy986a0XoXPfTQe3SrpnzZu2Jsp1U1Nwu2qfdiIxFx1Xvs3Duhsx7awzR5Q/wzfGg8Fz1NJST1GNznzDPEPp++rzLzkN+FeezmnyGh384F488lPmuW0tR7Xy+9Dz+nvndyJhjzEW+sViqY9nzLzUqx6XzsTpnn9wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"START DATE" = _t, #"END DATE" = _t, #"FIRST TYPE" = _t, #"SECOND TYPE" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"START DATE", "END DATE"}, "Attribute", "Value"),
    #"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Other Columns",{"START DATE", "END DATE", "Value"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Value] <> "/")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"START DATE", type date}, {"END DATE", type date}},"en-GB"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each {Number.From([START DATE])..Number.From([END DATE])}),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}})
in
    #"Changed Type1"

, and finally you can add a Calendar table to your data model.  With these prerequisites the chart creation is easy.

see attached

[Flo_19]

Hello @lbendlin,

I found the way to achieve my goal, in parts thanks to your proposition. 

I inspired a lot my work around your way to transform the file in power querry. 

After, I needed to obtain one row per Stop per month and calculate the prorata for each row.

Thanks you again for your help !

[Flo_19]

Hi @lbendlin, Thanks you for your very complete response !

I will check your solution in more details tomorrow. And see if it respond to my problem.

Thanks you again !