DAX to get the revenue for last 3 years along with the average of it

[Anonymous]

Hello,

I want to create a KPI to give me sum the revenue of the last 3 years and divide by 3.

Eg:

CALCULATE(sum(Table1[Orders_Revenue]),Prodtable[Product Level1] = "DPS", Fiscal Calendar[Calendar Year Aging]]] = {0,-1,-2},
Table4[SS Flag] >=1)) / 3

Any help will be appreciated.!

[bhanu_gautam · Accepted solution]

@Anonymous Try using

AverageRevenueLast3Years :=
VAR StartDate = CALCULATE(
MIN(FiscalCalendar[Date]),
FiscalCalendar[Calendar Year Aging] = -2
)
VAR EndDate = CALCULATE(
MAX(FiscalCalendar[Date]),
FiscalCalendar[Calendar Year Aging] = 0
)
RETURN
CALCULATE(
SUM(Table1[Orders_Revenue]),
Prodtable[Product Level1] = "DPS",
Table4[SS Flag] >= 1,
DATESBETWEEN(FiscalCalendar[Date], StartDate, EndDate)
) / 3

[Anonymous · Accepted solution]

Hi @Anonymous 

Thanks for the reply from bhanu_gautam .

It seems that you use four tables. I created a simple sample data for your reference. If this sample data is structurally different from the one you are using, could you please provide some sample data and expected results based on the sample data so that we can better help you? How to provide sample data in the Power BI Forum - Microsoft Fabric Community Please remove any sensitive data in advance.

My sample:

Create a measure

KPI_Revenue_Last_3_Years = 
CALCULATE(
    SUM(Table1[Orders_Revenue]),
    FILTER(
        Prodtable,
        Prodtable[Product Level1] = "DPS"
    ),
    FILTER(
        'Fiscal Calendar',
        'Fiscal Calendar'[Calendar Year Aging] IN {0, -1, -2}
    ),
    FILTER(
        'Table4',
        'Table4'[SS Falg] >= 1
    )
) / 3

Output:

Best Regards,
Yulia Xu

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

[bhanu_gautam · Accepted solution]

@Anonymous Try using

AverageRevenueLast3Years :=
VAR StartDate = CALCULATE(
MIN(FiscalCalendar[Date]),
FiscalCalendar[Calendar Year Aging] = -2
)
VAR EndDate = CALCULATE(
MAX(FiscalCalendar[Date]),
FiscalCalendar[Calendar Year Aging] = 0
)
RETURN
CALCULATE(
SUM(Table1[Orders_Revenue]),
Prodtable[Product Level1] = "DPS",
Table4[SS Flag] >= 1,
DATESBETWEEN(FiscalCalendar[Date], StartDate, EndDate)
) / 3