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

Question

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?

Answer 1

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