Forum Discussion
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...
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).
Hi ImkeF,
I had tried your suggestion, but its coming up as a circular dependancy error since my sum up field is also a calculated field.