Forum Discussion

azizshaikh's avatar
azizshaikh
Frequent Visitor
10 years ago

Opening Balance row wise

Hello All,   I need a help in my data where i wanted to get the Opening balance of each customer row wise and its being controlled by a report level date filter. eg. If my report level date filter...
waltheed's avatar
waltheed
Impactful Individual
10 years ago

Hi azizshaikh,

 

It does matter a lot what the filter context is, for using the OPENINGBALANCE functions.

 

Maybe this dax code sample helps you...

 

Opening Balance Qty :=
SWITCH (
    TRUE;
    ISFILTERED ( Date[Year Month] ) || ISFILTERED ( Date[Month] )
        || ISFILTERED ( Date[Month Short] )
        || ISFILTERED ( Date[Month Name] ); OPENINGBALANCEMONTH ( [Qty]; Date[Date] );
    ISFILTERED ( Date[Year Quarter] ) || ISFILTERED ( Date[Quarter] )
        || ISFILTERED ( Date[Quarter Naam] ); OPENINGBALANCEQUARTER ( [Qty]; Date[Date] );
    ISFILTERED ( Date[Year] ); OPENINGBALANCEYEAR ( [Qty]; Date[Date] );
    BLANK ()
)

 

So basically, with ISFILTERED yoiu check what the filter context is, and then you choose the correct OPENINGBALANCE function to use.

 

HTH,

azizshaikh's avatar
azizshaikh
Frequent Visitor
10 years ago

Hi waltheed

 

I tried your suggestion but again am getting circular dependancy error, not sure how exactly i should proceed further.

  • waltheed's avatar
    waltheed
    Impactful Individual
    10 years ago

    I think we need more info on your data model.

    Do you have a separate date table?