Advanced Formulae

[Naveeduddin]

Hi All,

I'm working on automating our excel reports to PowerBI and in few cases we have an incremental addition from day 1 and in another case an addition of one year above.

Attached below link has the sample data. Highlied in Yellow values need to be transformed in PowerBI (Column E & F), Kinldy help.

https://docs.google.com/spreadsheets/d/1GlcUWtb1hYPf8l1RVgIgz6JxTug30zobTo0BT_5nxL4/edit#gid=0

Regards

Naveed

[PaulDBrown · Accepted solution]

Create a dimension table for year and create a single to many relationship with the main table:

 Use this 'Year Table'[dYear] field in the visual.

Create and format the following measures:

Sum Existing = 
SUM(fTable[Existing])

Sum New = 
SUM(fTable[New])

Total = 
[Sum Existing] + [Sum New]

Running Total New =
CALCULATE (
    [Sum New],
    FILTER (
        ALL ( 'Year Table' ),
        'Year Table'[dYear] <= MAX ( 'Year Table'[dYear] )
    )
)

Renewal % =
VAR _PY =
    CALCULATE (
        [Running Total New],
        FILTER (
            ALL ( 'Year Table' ),
            'Year Table'[dYear]
                = MAX ( 'Year Table'[dYear] ) - 1
        )
    )
RETURN
    DIVIDE ( [Sum Existing], _PY )

Renewal % based on LY =
VAR _PY =
    CALCULATE (
        [Total],
        FILTER (
            ALL ( 'Year Table'[dYear] ),
            'Year Table'[dYear]
                = MAX ( 'Year Table'[dYear] ) - 1
        )
    )
RETURN
    DIVIDE ( [Sum Existing], _PY )

[PaulDBrown · Accepted solution]

Create a dimension table for year and create a single to many relationship with the main table:

 Use this 'Year Table'[dYear] field in the visual.

Create and format the following measures:

Sum Existing = 
SUM(fTable[Existing])

Sum New = 
SUM(fTable[New])

Total = 
[Sum Existing] + [Sum New]

Running Total New =
CALCULATE (
    [Sum New],
    FILTER (
        ALL ( 'Year Table' ),
        'Year Table'[dYear] <= MAX ( 'Year Table'[dYear] )
    )
)

Renewal % =
VAR _PY =
    CALCULATE (
        [Running Total New],
        FILTER (
            ALL ( 'Year Table' ),
            'Year Table'[dYear]
                = MAX ( 'Year Table'[dYear] ) - 1
        )
    )
RETURN
    DIVIDE ( [Sum Existing], _PY )

Renewal % based on LY =
VAR _PY =
    CALCULATE (
        [Total],
        FILTER (
            ALL ( 'Year Table'[dYear] ),
            'Year Table'[dYear]
                = MAX ( 'Year Table'[dYear] ) - 1
        )
    )
RETURN
    DIVIDE ( [Sum Existing], _PY )

[Naveeduddin · Accepted solution]

This is awesome! It worked, Thank you very much 🙂