Calculating column values based on two columns and problem in fetching last row's calculation

[ankit_cd]

Hello,

A newbie to Power BI here. I am facing a calculation problem and not sure where I am going wrong. I am trying to create a new column in a separate table where the idea is to calculate sum (total cost) for a particular 'year' and a particular 'type of cost'.

For example, from above table, I want to generate a single value of cost (Approved in Tariff Order) for a single year (2010) and a single type of cost (fixed). Currently I have input the following formula and it gives me the accurate sum but only for (n-1) years. The calculation for last year is somehow not being generated 

Fixed Costs  =

    CALCULATE(

        SUM(ARR_Approved[Approved in Tariff Order]),

        FILTER(ARR_Approved,ARR_Approved[Year]=APPC_Calculated[Year]),

        FILTER(ARR_Approved,ARR_Approved[Type of Cost]="Fixed")

    )

See the output column generated in Power BI as below:

Appreciate all the help I can get here. Thank you 🙂

[Anonymous · Accepted solution]

Hi @ankit_cd ,

Since you did not give a specific table, I had to create my own table for testing according to your description, please point out if there are any problems.

Please try below steps:

1. Below is my test table

Table1:

Table2:

2. In "Table2", add a new column with below dax formula

Total Cost =
VAR cur_year = Table2[Year]
VAR type_cost = "Fixed"
VAR tmp =
    FILTER (
        ALL ( Table1 ),
        Table1[Type of cost] = type_cost
            && YEAR ( Table1[Year] ) = cur_year
    )
RETURN
    SUMX ( tmp, [Cost] )

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.