Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
azizshaikh
Frequent Visitor

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.




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

 

Imke Feldmann (The BIccountant)

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

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

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.

Unfortunately not as I don't use that function.

Imke Feldmann (The BIccountant)

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

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

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.

Sean
Community Champion
Community Champion

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

 

Opening Balance.png

@Sean Smiley LOLSmiley LOLSmiley LOLSmiley LOL

@Sean: Got a new book mate 😉

Imke Feldmann (The BIccountant)

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

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

waltheed
Solution Supplier
Solution Supplier

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
ITsmart BI and Analytics consultant

Hi @waltheed

 

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

I think we need more info on your data model.

Do you have a separate date table?

 

 

 

Cheers, Edgar Walther
ITsmart BI and Analytics consultant
Anonymous
Not applicable

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

 

 

Greg_Deckler
Super User
Super User

Have you tried creating a measure based upon OPENINGBALANCEYEAR?

 

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.