Forum Discussion
Aggrate Sales with Date
Hi,
It's something I've not done in sometime so I'm not sure the correct way to do it, here is my current issue :
I'm trying to get the Budget on a date that correspond to the last time the item was sold. It work well in a table seperated by item, but not when aggregated together.
| table1 | table2 | |||
| Sold Date | Budget Date | Budget | ||
| Item1 | 9/30/2024 | Item1 | 9/30/2024 | 5 |
| item2 | 9/26/2024 | item2 | 9/26/2024 | 10 |
| item2 | 9/30/2024 | 15 |
The total budget should be 15, but it grab the last sold date available instead of last date by item which result in an incorrect result.
Any idea ? Thanks !
7 Replies
Hi,
Sorry If I explained it incorrectly.
I'm looking for :
Total Budget = 15 (both item combined)
Currently I get 20 since it take the last date available (9/30/2024) from Item 1 and apply it for item 2 as well.
No it wont work,
My budget table date goes past my actual sales date, I need to get back the budget on the latest actual sales date.
Currently I have :TEST_budget_last_date = CALCULATE(max(data[Budget]),FILTER(data,LTP_data[Actual Date] = max(Sales'[SalesDate])))
Working well in a table, but not in a card.Hi LC01 ,
The issue could be related to context transition when using the CALCULATE() function,try below:
TEST_budget_last_date = VAR LatestSalesDate = MAX(Sales[SalesDate]) RETURN CALCULATE( MAX(data[Budget]), FILTER( data, data[Actual Date] = LatestSalesDate ) )Hope it helps!
Best regards,
Community Support Team_ Scott ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
- parry2k
Super User
LC01 Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 - Ashish_Mathur
Hi,
Share some sample data to work with and show the expected result. Share data for a few items.
