Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create rental steps by comparing dates across rows

I have a list of contracts for rental properties. On each contract is a list of subscription lines detailing how much the customer will be paying across a certain timeframe - indicated by SBQQ__St...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

v-juanli-msft Yes that custom visual looks good, however, I just want to get the data into an appropriate format for excel export. Further down the line I may use the stepped line visual.

And yes, the table is correct. Using your table, I would want it to look like the following format (i haven't filled out every step)


contract numberproductnamestart dateend dateamount (£)
1full rent8/26/20148/25/202060k
1half rent8/26/202012/25/202030k
1full rent12/26/20208/25/202160k
1half rent8/26/202112/25/202130k
1full rent………
 …………
2full rent8/26/20198/25/202070k
2half rent8/26/202012/25/202035k
 …………

 

The key assumptions are:

a) Full rent will always be the first step on a contract

b) amount (£) is a field direct from datasource, no need to calc

v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

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.