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...
azizshaikh's avatar
azizshaikh
Frequent Visitor
10 years ago

Hello All,

 

We are experiencing a roadblock while trying to implement a feature for getting Opening balance for a particular customer within a certain date range.

To explain it more clearly, let’s take a example:

 

Sale Order Table

Order Date         CustName         Sale (USD$)

03/24/2016               Aziz               100

04/02/2016               Aziz               250

04/10/2016               Aziz               200

04/11/2016               Aziz               100

04/06/2016               Ankit              200

04/09/2016               Ankit              100

04/11/2016               Ankit              150

 

For the above table we will going to apply a Start and End date filter on Report level for Order Date and following result is expected from the table.

  1. Start date: 04/07/2016

         End date: 04/12/2016

 

Sale Order Report

CustName    Opening Balance     Sale (USD$)     Closing Balance

Aziz                       350                          300                      650

Ankit                      200                          250                      450

 

So as seen from the above table, after applying report level filter the opening balance should come up as a sum up of balance before(i.e. 04/06/2016) the start date of the filter.

We had already tried with the combination of Time Intelligence Functions like “OPENBALANCEYEAR”, “TOTALYTD” etc. but didn't got the desired output.

Kindly suggest a solution or a workaround for the above scenario.




  • 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).

     

    • Sean's avatar
      Sean
      Community Champion
      10 years ago

      I believe this is the 1st time I've ever seen ImkeF solve something with DAX and not M! :smileyhappy:

       

      • GilesWalker's avatar
        GilesWalker
        Skilled Sharer
        10 years ago

        Sean :smileylol::smileylol::smileylol::smileylol:

    • azizshaikh's avatar
      azizshaikh
      Frequent Visitor
      10 years ago

      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.

    • 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.