Forum Discussion

paolomint's avatar
paolomint
Helper III
6 years ago
Solved

Standard Financial report

Hello everyone, I'm working on a financial report in Power Bi. Unfortunately my boss also ask for a traditional overview of the data. This my table:           I need to add, EBITDA a...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, paolomint 

     

    Based on your description, I add new data in the table.

    Table:

    Test:

     

    Year(a calculated table):

     

    Year = DISTINCT('Table'[Year])

     

     

    Here are the measures.

     

    CurrentYear = 
var _info = SELECTEDVALUE(Test[Info])
var _year = SELECTEDVALUE('Year'[Year])
var _revenues = 
CALCULATE(
        SUM('Table'[Revenues]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Year] = _year
        )
)
var _directcost = 
CALCULATE(
        SUM('Table'[Direct cost]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Year] = _year
        )
)
var _indirectcost = 
CALCULATE(
        SUM('Table'[Indirect cost]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Year] = _year
        )
)
var _capex = 
CALCULATE(
    SUM('Table'[Capex]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Year] = _year
    )
)
return
SWITCH(
    TRUE(),
    _info = "Revenues",
    _revenues,
    _info = "Direct cost",
    _directcost,
    _info = "Indirect cost",
    _indirectcost,
    _info = "Ebitda",
    _revenues-_directcost-_indirectcost,
    _info = "Capex",
    _capex,
    _info = "Ebit",
    _revenues-_directcost-_indirectcost-_capex,
    BLANK()
)

LastYear = 
var _info = SELECTEDVALUE(Test[Info])
var _year = SELECTEDVALUE('Year'[Year])-1
var _revenues = 
CALCULATE(
        SUM('Table'[Revenues]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Year] = _year
        )
)
var _directcost = 
CALCULATE(
        SUM('Table'[Direct cost]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Year] = _year
        )
)
var _indirectcost = 
CALCULATE(
        SUM('Table'[Indirect cost]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Year] = _year
        )
)
var _capex = 
CALCULATE(
    SUM('Table'[Capex]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Year] = _year
    )
)
return
SWITCH(
    TRUE(),
    _info = "Revenues",
    _revenues,
    _info = "Direct cost",
    _directcost,
    _info = "Indirect cost",
    _indirectcost,
    _info = "Ebitda",
    _revenues-_directcost-_indirectcost,
    _info = "Capex",
    _capex,
    _info = "Ebit",
    _revenues-_directcost-_indirectcost-_capex,
    BLANK()
)

Diff = [CurrentYear]-[LastYear]

Result rate = 
DIVIDE(
    [CurrentYear]-[LastYear],
    [LastYear]
)

     

     

    You may use a matrix visual to display the result.

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.