Split Data rows into multiple rows based on date

[Anonymous]

Hi All,

I have a dataset which has revenue generated by certain clients over their annual cycle since they became a client - for example say 1st April 2021 to 31st March 2022. But could be less or more than a year on some occasions.

I am trying to split revenue for these accounts into calander years and proportion the revenue accordingly. Any idea how to do this in Power Query:

So data looks like this:

Client	Start Date	End Date	Duration (Days)	Revenue
Client A	01/04/2021	31/03/2022	365	15000
Client B	01/01/2021	31/12/2021	365	20000
Client C	01/11/2020	01/02/2022	458	25000

And would like an output like this:

Client	Year	Duration (Days)	Revenue
Client A	2021	275	11301
Client A	2022	90	3699
Client B	2021	365	20000
Client C	2020	61	3330
Client C	2021	365	19924
Client C	2022	32	1747

Cant get my head around how I would do this. Any help would be appreciated.

James 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[lbendlin]

here is one possible implementation assuming your data is immutable (ie using calculated columns)

Days =
SUMX(
    FILTER( 'Table', 'Table'[Client] = Cross[Client] ),
    COUNTROWS(
        INTERSECT(
            CALENDAR( [Year] & "-01-01", [Year] & "-12-31" ),
            CALENDAR( [Start Date], [End Date] )
        )
    )
)

Revenue =
[Days]
    * SUMX(
        FILTER( 'Table', 'Table'[Client] = Cross[Client] ),
        [Revenue] / COUNTROWS( CALENDAR( [Start Date], [End Date] ) )
    )