How to make a measurement with a rolling/moving conditional in DAX Excel PowerPivot or Power BI

0
votes

I have this table:

Period Firm Sector Net Income Assets
31 Dec 2018 AA 1 10 100
31 Dec 2018 BB 1 20 100
31 Dec 2018 CC 2 30 100
31 Dec 2018 DD 2 40 100
31 Dec 2019 AA 1 15 100
31 Dec 2019 BB 1 25 100
31 Dec 2019 CC 2 35 100
31 Dec 2019 DD 2 45 100
31 Dec 2020 AA 1 18 100
31 Dec 2020 BB 1 null 100
31 Dec 2020 CC 2 38 100
31 Dec 2020 DD 2 48 null

I want to create a measurement to calculate the sectoral Return on Assets, i.e. SUM(Net Income)/SUM(Assets) in year t , to include only firms which have a complete set of Net Income and Assets in year t and year t-1.

Hence, I want to create a pivot table like this:

ROA Sector
Period 1 2
31 Dec 2018 null null
31 Dec 2019 20 % 40%
31 Dec 2020 18 % 38%

How can I do that in DAX?

1
excelpowerbidaxpowerpivot

1 Answers

0
votes

A possible solution is to prepare a table variable containing the previous year set to be used as a filter over the current year calculation.

ROA = 
VAR CurrentPeriod =
    SELECTEDVALUE ( T[Period] )
VAR PreviousPeriod =
    DATEADD (
        T[Period],
        -1,
        YEAR
    )
VAR Result =
    IF (
        NOT ISBLANK( CurrentPeriod ),
        VAR PreviousYearSet =
            CALCULATETABLE (
                SUMMARIZE (
                    T,
                    T[Sector],
                    T[Firm]
                ),
                NOT ISBLANK ( T[Assets] ),
                NOT ISBLANK ( T[Net Income] ),
                T[Period] = PreviousPeriod
            )
        RETURN
            CALCULATE (
                DIVIDE (
                    SUM ( T[Net Income] ),
                    SUM ( T[Assets] )
                ),
                NOT ISBLANK ( T[Assets] ),
                NOT ISBLANK ( T[Net Income] ),
                PreviousYearSet
            )
    )
RETURN
    Result

this measure applied to the sample data can be used in a matrix visual to give the expected result

the resulting matrix