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...
ImkeF's avatar
ImkeF
Community Champion
10 years ago

If my understanding is correct, you want an opening balance with all transactions before that date, not just beginning from the start of the year (like year-to-date (YTD) ) - right?

Then I would go with a simple stupid Cumulative Total Pattern like this:

 

OpeningBalance:=CALCULATE(SUM(SaleOrder[Sale]);FILTER(ALL(Date[Date]);Date[Date]<MIN(Date[Date])))

and

 

 

ClosingBalance:=CALCULATE(SUM(SaleOrder[Sale]);FILTER(ALL(Date[Date]);Date[Date]<=MAX(Date[Date])))

This means that just the Date-contexts will be ignored and replaced by the filter-expression and all other elements will be taken into account in your pivots (like customer name or others).

 

anandav's avatar
anandav
Skilled Sharer
9 years ago

Hi ImkeF

Thanks for the formulas. Using your DAX formulas I was able to get the opening and closing balance.

 

I was trying to use the below DAX functgion and couldn't get the opening balance. 

OPENINGBALANCEMONTH(<expression>,<dates>[,<filter>])

Do you have any example how to use the OPENINGBALANCEMONTH function?

Thanks in advance.

  • ImkeF's avatar
    ImkeF
    Community Champion
    9 years ago

    Unfortunately not as I don't use that function.