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

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

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 Madison Power BI User 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
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.