Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all!
I've read through some similar use cases, but can't seem to nail down a calculation that works. I'm trying to sum a quantity field for all dates before the earliest selected. For example, user selects date range 2/1/2019 - 2/28/2019. The opening balance should sum quantities for all dates before 2/1/2019. Seems straightforward, but I must be missing something.
I've done something, such as:
Solved! Go to Solution.
Will ALL() you can specify columns too, may be a shorter list for you. Check this link.
Hi @Anonymous ,
You need an extra date dimension table which is unrelated to 'Item' and 'Item status' table.
calendar = CALENDAR ( MIN ( 'Item status'[Status DateTime] ), MAX ( 'Item status'[Status DateTime] ) )
Opening Balance = VAR first_date = MIN ( 'calendar'[Dates] ) RETURN CALCULATE ( SUM ( 'Item'[Quantity] ), FILTER ( 'ItemStatus', 'Item Status'[Status] = "Created" ), FILTER ( ALL ( 'Item Status' ), 'Item Status'[Status DateTime] <= first_date ) )
Hi @Anonymous ,
You need an extra date dimension table which is unrelated to 'Item' and 'Item status' table.
calendar = CALENDAR ( MIN ( 'Item status'[Status DateTime] ), MAX ( 'Item status'[Status DateTime] ) )
Opening Balance = VAR first_date = MIN ( 'calendar'[Dates] ) RETURN CALCULATE ( SUM ( 'Item'[Quantity] ), FILTER ( 'ItemStatus', 'Item Status'[Status] = "Created" ), FILTER ( ALL ( 'Item Status' ), 'Item Status'[Status DateTime] <= first_date ) )
Try something like this:
Hi,
Thanks for the reply.
I think this is not working in my scenario because using the ALL to over ride the date context also encapsulates all items, which I do not necessarily want.
The fact table actually contains each item 1 time, with an associated qty. This is related 1:M to the Item Status table, where the item can have multiple statuses and multiple dates. Item 001 can be created on 12/1, blocked on 12/2, shipped on 12/4, etc.
The matrix/table on the summary page has the Opening Blance ("pre-sum" in your example) by Item Category, this is another dimension.
I think using ALL on the Status table removes the current context of item category in the table grid and throws everything off. Hoepfully I explained that correctly 🙂
So maybe use ALLEXCEPT([DateColumn]), so you can keep your status and other filters?
Hi there,
I actually need the opposite of ALLEXCEPT. I want to maintain all current filters/row context, but override the current date context for Status Date. That's why I tried to use ALL, but maybe I need to combine it with something else.
When I implemented ALLEXCEPT('Item Status', "Item Category[Category Name], Company [Company Name]) ..and so on, the calculation is appearing correct! But I'd need to add every single filter column into the ALLEXCEPT function. There are about 5 - 6 drop down filters on the report header. Thanks for getting me this far!
Will ALL() you can specify columns too, may be a shorter list for you. Check this link.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |