Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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.
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: 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
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,
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?
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)
Have you tried creating a measure based upon OPENINGBALANCEYEAR?
https://msdn.microsoft.com/en-us/library/ee634886.aspx
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
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |