Forum Discussion

Naveeduddin's avatar
Naveeduddin
Frequent Visitor
3 years ago
Solved

Advanced Formulae

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

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    3 years ago

    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's avatar
    Naveeduddin
    3 years ago

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

2 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    3 years ago

    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's avatar
      Naveeduddin
      Frequent Visitor
      3 years ago

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