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 o...
Need Help
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] ) )
    )