cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
azizshaikh
Frequent Visitor

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 is set between 1 jan 2016 to 31 dec 2016, then the opening balance should come up with a result of balance total on and before 31st dec 2015 for that particular customer.

15 REPLIES 15
Super User IV
Super User IV

Re: Opening Balance row wise

Have you tried creating a measure based upon OPENINGBALANCEYEAR?

 

https://msdn.microsoft.com/en-us/library/ee634886.aspx

 


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

adamflath Resolver I
Resolver I

Re: Opening Balance row wise

Opening Balance Year should work as that is how I do mine:

 

Start of Year Indemnity Paid = OPENINGBALANCEYEAR([INDEMNITY Paid Amount],'Summary Financials'[Report Eval Date])

 

You can also confirm this works by dragging over the YEAR attribute in your Calendar Table and checking to see what the Opening balance is across those years to make sure it is accurate.

 

Another confirmation is to take multiple date in the year and make sure the result is all the same number (opening balance)

opening balance.png

 

 

azizshaikh
Frequent Visitor

Re: Opening Balance row wise

Hello,

I had tried OPENINGBALANCEYEAR in measure but it came up with a blank value.

Let me share an example:

 

Name     Opening Balance     Column1     Column2     Closing Balance

1st person        2500                           100              50                2650

2nd person       1500                           150            100                1750

 

In above example each person will have its own opening balance based on the dates which will be passed by the report level filter, so if am setting a report filter from 01/01/2016 to 25/04/2016 then the opening balance should show up till the 31/12/2015 for "1st person" and "2nd person" respectively.

 

I had tried with some of the Time Intelligence functions but couldn't get desired result, as it mostly gives me Circular Dependency error.

 

Thanks

achinm45 Advocate III
Advocate III

Re: Opening Balance row wise

Hi @azizshaikh

Can you send me the screenshot of your measure or calculation ?

I will need to know how are you putting filters dates. Is that single column or two columns ?

 

BR,
Achin

 

 

waltheed Solution Supplier
Solution Supplier

Re: Opening Balance row wise

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,

Cheers, Edgar Walther
Power BI User Group Netherlands
azizshaikh
Frequent Visitor

Re: Opening Balance row wise

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.




Super User III
Super User III

Re: Opening Balance row wise

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

 

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

 
Sean Community Champion
Community Champion

Re: Opening Balance row wise

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

 

Opening Balance.png

Super User III
Super User III

Re: Opening Balance row wise

@Sean: Got a new book mate 😉

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors