DAX - Sum rows with the latest dates across multiple columns

Question

I am new to PowerPivot / DAX and am analyzing a table named BudgetTable with 5 columns. When the budgeted amount changes for an Item and Attribute combination, users add a new row to the table with a new record date. In the table below, for example, the 2/1/19, 3/1/19, 4/1/19 and 5/1/19 records are updates of prior Item and Attribute combinations.

ID  Item  Attribute   Budget Amount  Record Date
1     A      AA          $1000         1/1/19
2     A      AA          $2000         2/1/19
3     A      BB          $3000         1/1/19
4     A      BB          $4000         3/1/19
5     B      AA          $5000         1/1/19
6     B      AA          $6000         5/1/19
7     B      BB          $7000         1/1/19
8     B      BB          $8000         4/1/19

I would like to define a measure (Sum of Budget Amount) that sums up the Budget Amounts using the latest date for each Item and Attribute combination.

I have tried:

Sum of Budget Amount:=CALCULATE([BudgetTable], FILTER(BudgetTable, BudgetTable[Record Date]=Max(BudgetTable[Record Date])

but, that produces a pivot table that looks like:

Item   Attribute   Sum of Budget Amount
 A                        $4000
           AA             $2000
           BB             $4000
 B                        $6000
           AA             $6000
           BB             $8000

The expected pivot table would look like:

Item     Attribute   Sum of Budget Amount
 A                        $6000
           AA             $2000
           BB             $4000
 B                       $14000
           AA             $6000
           BB             $8000

Any help would be appreciated. Thank you!

Answer 1

1 - Create Dimension from your your fact table:

      Calendar = CALENDARAUTO()
      ITEM = VALUES(Budget[Item])
      ATTRIBUTE = VALUES(Budget[Attribute])

2 - Create your model setting the relationship:

Modelling

3 - Create the mesure that answer the question :

Total Budget = 
  SUMX(
     'ITEM';
      SUMX(
         'ATTRIBUTE';
          CALCULATE(
             Sum(Budget[Budget Amount]);
             LASTNONBLANK(
                 'Calendar'[Date];
                 COUNTROWS(RELATEDTABLE(Budget))
             )
         )
     )
 )

4 - Result

Here you can see the result

Answer 2

I would do the following:

Measure =
// SUMX iterates the table in its arg1, evaluating the expression in arg2
// for each row of the table.
SUMX (
    // Output of GROUPBY is a two-column table of unique combinations
    GROUPBY (
        'BudgetTable',
        'BudgetTable'[Item],
        'BudgetTable'[Attribute]
    ),
    VAR MaxDate = CALCULATE ( MAX ( 'BudgetTable'[Record Date] ) ) // max date for current row
    RETURN
        CALCULATE (
            'SUM ( 'BudgetTable'[Amount] ),
            'BudgetTable'[Record Date] = MaxDate
        )
)

It is a good idea to dimensionalize your model per the response from @JBfreefolks, but it is not necessary for this specific measure.