cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sicilian Regular Visitor
Regular Visitor

Sum an opening balance before the selected filter date range

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:

 

FirstDate = CALCULATE(
    FIRSTDATE('Item Status'[Status DateTime]),
    ALLSELECTED('Item Status'[Status DateTime])
)

 

Opening Balance = VAR first_date=[firstDate]
RETURN CALCULATE(
    SUM('Item'[Quantity]),FILTER('ItemStatus', 'Item Status'[Status] = "Created"),
    FILTER(ALL('Item Status'),
        'Item Status'[Status DateTime] <= first_date
    ))
 
Note that the Item is the fact table, which has one record per item, with an associated qty of that item.
The Item Status table contains many instances of the same item for each status the item can go through, along with the date/time of that status. For example, created, shipped, deleted, etc.
 
I've also tried the above, but replace FirstDate with "Current Status DateTime"
 
Current Status DateTime =
var currentSelection = CALCULATE(MIN('Asset Status'[Status DateTime]))
return
MINX(ALLSELECTED('Asset Status'[Status DateTime]),
IF('Asset Status'[Status DateTime] < currentSelection, 'Asset Status'[Status DateTime], BLANK()))
statusdates.PNG
Above I've just manually applied a filter to show status dates before 2/1/2019, I'd expect all of those quantities to show up in my measure on the summary view of the report (which is actually broken out by Item Category). 
 
Hopefully this makes sense. Any input is graciously appreciated!
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Sum an opening balance before the selected filter date range

Hi @Sicilian ,

 

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] )
)
The formula to sum up quantities for all dates before the earliest selected date could be:

 

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 )
    )
For more advice, please post sample data in each source data table, including 'Item', 'Item status', 'Item Category', and show us desired output. How to Get Your Question Answered Quickly
 
Best regards,
Yuliana Gu
Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Aron_Moore Established Member
Established Member

Re: Sum an opening balance before the selected filter date range

Try something like this:

 

Pre Sum = CALCULATE(SUM(Table1[Qty]),FILTER(ALL(Table1),Table1[Date]<=MIN(Table1[Date])))

 

Capture.PNGCapture2.PNG

Sicilian Regular Visitor
Regular Visitor

Re: Sum an opening balance before the selected filter date range

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 Smiley Happy

Aron_Moore Established Member
Established Member

Re: Sum an opening balance before the selected filter date range

So maybe use ALLEXCEPT([DateColumn]), so you can keep your status and other filters?

Highlighted
Sicilian Regular Visitor
Regular Visitor

Re: Sum an opening balance before the selected filter date range

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! 

Aron_Moore Established Member
Established Member

Re: Sum an opening balance before the selected filter date range

Will ALL() you can specify columns too, may be a shorter list for you. Check this link.

Community Support Team
Community Support Team

Re: Sum an opening balance before the selected filter date range

Hi @Sicilian ,

 

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] )
)
The formula to sum up quantities for all dates before the earliest selected date could be:

 

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 )
    )
For more advice, please post sample data in each source data table, including 'Item', 'Item status', 'Item Category', and show us desired output. How to Get Your Question Answered Quickly
 
Best regards,
Yuliana Gu
Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.