Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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?
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?
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
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.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |