Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Split Data rows into multiple rows based on date

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:

ClientStart DateEnd DateDuration (Days)Revenue
Client A01/04/202131/03/202236515000
Client B01/01/202131/12/202136520000
Client C01/11/202001/02/202245825000

 

And would like an output like this:

ClientYearDuration (Days)Revenue
Client A202127511301
Client A2022903699
Client B202136520000
Client C2020613330
Client C202136519924
Client C2022321747

 

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

James 

 

Need Help

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

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