Max date orginised

[ThomasWeppler]

Hi power Bi community

I have a measure that looks at the last budgetentry for a selected date.

My problem is when two entries have been entered the same date they are both added together, I only need the last one.

This happens when someone write something wrong by mistake and edit it right away.

My DAX is.

Real budget =

VAR __max_date = MAX('Calender'[Date])

VAR __max_budgetentry_date =

    CALCULATETABLE(

        ADDCOLUMNS(

            VALUES('4072 BudgetEntry'[task and budget]),

            "@MaxBudgetEntryDate",

            CALCULATE(MAX('4072 BudgetEntry'[Date]))

        ),

        'Calender'[Date]<=__max_date

    )

VAR __max_date_with_taskandbudget =

    TREATAS(

    __max_budgetentry_date,'4072 BudgetEntry'[task and budget],'calender'[Date]

    )

VAR __result =

    CALCULATE(SUM('4072 BudgetEntry'[fixed amount]),__max_date_with_taskandbudget)

RETURN

    __result

In my table I have a colum called entry_id and when I have multiple rows I only need the row with the highest entry_id.

but if a calender date is choosen in the slicer that says 30/09/2024 I should not include rows after that date.

I hope all of this make sense.

[Anonymous]

Hi @ThomasWeppler 

Maybe you can try this:

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

This measure should now return the latest budget entry for each date, respecting the constraints you outlined.

Hope this can help,

Best Regards

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

[ThomasWeppler · Accepted solution]

In the end I changed the code in the sql server so I only got the relevant data in PowerBI and than it worked with the DAX I already had.

But thanks a ton to all the people who came with suggestions and tried to help me. I have given all of you a kudo as a thanks. 🙂

[ThomasWeppler]

Hi @Anonymous 
Thanks for the suggestion.
I tried it and I got the exact same result as I did in my code.

[saud968]

It sounds like you need to modify your DAX measure to ensure that only the last entry for each date is considered, specifically by using the highest entry_id for entries on the same date. Here's an updated version of your DAX measure:

Real budget =
VAR __max_date = MAX('Calender'[Date])
VAR __max_budgetentry_date =
CALCULATETABLE(
ADDCOLUMNS(
VALUES('4072 BudgetEntry'[task and budget]),
"@MaxBudgetEntryDate",
CALCULATE(MAX('4072 BudgetEntry'[Date]))
),
'Calender'[Date] <= __max_date
)
VAR __max_date_with_taskandbudget =
TREATAS(
__max_budgetentry_date, '4072 BudgetEntry'[task and budget], 'Calender'[Date]
)
VAR __latest_entry =
CALCULATETABLE(
TOPN(
1,
FILTER(
'4072 BudgetEntry',
'4072 BudgetEntry'[Date] <= __max_date
),
'4072 BudgetEntry'[entry_id], DESC
)
)
VAR __result =
CALCULATE(
SUM('4072 BudgetEntry'[fixed amount]),
__latest_entry
)
RETURN
__result
Explanation:
Filter by Date: Ensure that only entries up to the selected date are considered.
Select Latest Entry: Use TOPN to select the row with the highest entry_id for each date.
Calculate Result: Sum the fixed amount for the latest entry.
This should ensure that only the last entry (based on entry_id) for each date is included in your calculation.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[AMeyersen]

Hi,

I think you can change the last part of your formula.

Instead of

VAR __result =

    CALCULATE(SUM('4072 BudgetEntry'[fixed amount]),__max_date_with_taskandbudget)

you can use something like this:

VAR __result =

    CALCULATE(LASTNONBLANKVALUE('4072 BudgetEntry'[entry_id],SUM('4072 BudgetEntry'[fixed amount])),__max_date_with_taskandbudget)

That way you should be able to not return the sum of several rows but only the one with highest [intry_id].