Forum Discussion
Solved
Max date orginised
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 ...
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. 🙂
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.
Hi Anonymous
Thanks for the suggestion.
I tried it and I got the exact same result as I did in my code.