Forum Discussion
Max date orginised
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. 🙂
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!