cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sebastian
Advocate II
Advocate II

opening balance and ending balance of article sorted by Warehouse and Year

Hi Community,

 

I try to get the opening and ending balance of each article sorted by Year and warehouse.

 

My table looks like:

 

Article   Date              Warehouse        amount

1         03.01.2014       100                    50

1         15.08.2014       100                    15

1         15.01.2015       100                    100

1         20.05.2015       100                    50

1         08.08.2015       200                    10

2         05.05.2015       100                    30

2         10.10.2015       200                    50

 

Could anyone help?

 

Thanks Sebastian

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

Tough to say with specificity without knowing exactly what your data means. Do you need to sum these numbers or are you just looking for the first date and the last date in a year?

 

Several possible DAX functions could help here:

OPENINGBALANCEYEAR and CLOSINGBALANCEYEAR

FIRSTNONBLANK and LASTNONBLANK

STARTOFYEAR and ENDOFYEAR coupled with a filter function or CALCULATE

 

What should the answer be for warehouse 100 and 200?


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

Tough to say with specificity without knowing exactly what your data means. Do you need to sum these numbers or are you just looking for the first date and the last date in a year?

 

Several possible DAX functions could help here:

OPENINGBALANCEYEAR and CLOSINGBALANCEYEAR

FIRSTNONBLANK and LASTNONBLANK

STARTOFYEAR and ENDOFYEAR coupled with a filter function or CALCULATE

 

What should the answer be for warehouse 100 and 200?


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

I need the opening and ending balance of every article which is in the warehouse from each year.

 

Thanks for your advice. I didn't know the DAX functions "openingbalanceyear" and "closingbalanceyear".

Now it still works.

 

Thanks.

that's worse.

 

Try to get the start and end balance of every article for each year.

Now i have use the DAX functions openingbalanceyear and closingbalanceyear but it doesn't work.

 

Could anybody give an example how to use the functions?

The result should looks like:

 

Article   Year              opening balance                   ending balance

1              2015          75 (end of last year)               235 (sum of current Year + opening balance)

Correct me if I'm wrong, but it sounds like the 'amount' column is not the balance on each day but a transaction amount.

 

If so, that would make your starting/ending balances more like cumulative totals (as in you want to sum the amount column for all dates up to and including the starting/ending date).

 

Do you have a date table? (A table of consecutive dates with no gaps)

 

If so, you can do a cumulative total with something like:

 

CumulativeAmount =
CALCULATE (
    SUM ( sheet1[amount] ),
    FILTER ( ALL ( dimdate[Date] ), dimdate[Date] <= MAX ( DimDate[Date] ) )
)

*sheet1 = the name of your table (substitute as needed)

*dimdate = the date table (with a column date inside it)

 

In a pivot table, if you slice cumulative total by year, you'll essentially get the ending balance for each year.

 

You can then use the openingbalanceyear measure to ask for the cumulative balance as of the start of the year:

 

OpeningBal :=
OPENINGBALANCEYEAR ( [CumulativeAmount], Table1[date] )

(There is also a CLOSINGBALANCEYEAR if you need it)

 

Hope this helps

---
In Wisconsin? Join the Microsoft BI Professionals - Wisconsin group.
Anonymous
Not applicable

How I can do cumulative sum only for one year. 

 

Data: 

Month   |  Sales

Nov 18  |  10

Dec 18  | 20

Jan 19   | 30

Feb 19   | 40

Mar 19   |50

 

I only need the cumulative sum to do: 30+40+50

 

Can you help me please?

Thank You very much leonardmurphy. This is exactly what I'm googling for. I am very new in power BI and this really help me.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.