Create rental steps by comparing dates across rows

Hi Anonymous 

In your table,

1. open Edit queries, first sort by [number], second sort by [start date], then add an index column from 1.

2. close&&apply

3. create calculated columns

flag =
VAR min_ =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        ALLEXCEPT (
            'Table',
            'Table'[number]
        )
    )
VAR max_ =
    CALCULATE (
        MAX ( 'Table'[Index] ),
        ALLEXCEPT (
            'Table',
            'Table'[number]
        )
    )
RETURN
    SWITCH (
        [Index],
        min_, "min",
        max_, "max"
    )

add start =
SWITCH (
    TRUE (),
    [flag] <> "min"
        && [productname] = "half rent", [end date] + 1,
    [flag] = "min", [start date]
)


add end =
CALCULATE (
    MIN ( 'Table'[start date] ),
    FILTER (
        ALLEXCEPT (
            'Table',
            'Table'[number]
        ),
        'Table'[Index]
            = EARLIER ( 'Table'[Index] ) + 1
    )
) - 1

4.create a new table with dax codes below

Table 3 =
UNION (
    FILTER (
        SUMMARIZE (
            'Table',
            [number],
            'Table'[productname],
            'Table'[start date],
            [end date],
            'Table'[amount]
        ),
        [productname] <> "full rent"
    ),
    SELECTCOLUMNS (
        'Table',
        "number", [number],
        "productname", "full rent",
        "start date", [add start],
        "end date", [add end],
        "amount", [amount]
    )
)

5.add columns to a table visual, then "export data".

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.