Only show value for the last date

[ThomasWeppler]

Hi Power Bi community

I have a budget table where all new data just gets added into the table so each edit add more data to the table.

The table looks like this 

Budgetentry[fixed amount] = the amount of money

Budgetentry[date] = the date the entry has been added
Budgetentry[maintask_id] = the rows that i use in the report

Budgetentry[task_id] = a main task can have multiple task_id connected to them and each one can have a budget

Budgetentry[budget_id] = Every task can have multiple budgets.

Budgetentry[task and budget] = '4072 BudgetEntry'[Task_id] & "-" & '4072 BudgetEntry'[Budget_id] = a uniq id that hopefully can help me not include the same budget twice.

I want to filter the data with a date slicer that allows me to only look at the latest uniq Budgetentry[task and budget] Since multiples always are an example of an edit and never new data. My dax also need to work so if I pick an older max date I can see the old budget.

So I have wrote this in Dax

Test =

CALCULATE(

    CALCULATE(SUM('4072 BudgetEntry'[Fixed amount]), lastDATE('4072 BudgetEntry'[Date])))

But it doesn't work. I think the problem is that lastdate() sometimes filter for the wrong date.

Can some smart and cool powerBi expert help me move forward with this problem? All help is greatly appreciated.

[Anonymous · Accepted solution]

Hi @ThomasWeppler ,

Please try this:

Measure = 
VAR __max_date = MAX('Date'[Date])
VAR __max_budgetentry_date = 
    CALCULATETABLE(
        ADDCOLUMNS(
            VALUES('4072 BudgetEntry'[task and budget]),
            "@MaxBudgetEntryDate",
            CALCULATE(MAX('4072 BudgetEntry'[date]))
        ),
        'Date'[Date]<=__max_date
    )
VAR __max_date_with_taskandbudget = 
    TREATAS(
    __max_budgetentry_date,'4072 BudgetEntry'[task and budget],'Date'[Date]
    )
VAR __result =
    CALCULATE(SUM('4072 BudgetEntry'[fixed amount]),__max_date_with_taskandbudget)
RETURN
    __result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[ThomasWeppler]

Thanks @Anonymous 
It is really impressive how easy your solution was to impliment and how well it worked.

Thanks a lot.

[rajendraongole1]

Hi @ThomasWeppler - Your approach to summing the latest entries in Power BI can be refined to ensure LASTDATE() calculates correctly based on your unique budget entries. 

check the below dax measure:

Latest Budget Amount =
CALCULATE(
SUM('4072 BudgetEntry'[fixed amount]),
'4072 BudgetEntry'[Date] =
CALCULATE(
MAX('4072 BudgetEntry'[Date]),
ALLEXCEPT('4072 BudgetEntry', '4072 BudgetEntry'[task and budget])
)
)

This refined DAX should provide the latest entries for each task and budget accurately, respecting the slicer context.