Sum an opening balance before the selected filter date range

[Anonymous]

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()))

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!

[Aron_Moore · Accepted solution]

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

[v-yulgu-msft · Accepted solution]

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] )
)

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

[Aron_Moore]

Try something like this:

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

[Anonymous]

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 🙂

[Aron_Moore]

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

[Anonymous]

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 · Accepted solution]

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