Forum Discussion
Power Pivot DAX Balance sheet averages
Hi Newbie here.
I'm progressing quite well with preparing a Profit and loss model using power pivot, but am struggling with some of the balance sheet calculations.
In Simple form my data is structured something like this:
Date Desc BF Amount
01/01/18 Stock 1 1,000
01/01/18 Stock 0 -200
01/02/18 Stock 0 +500
01/03/18 Stock 0 +600
Opening stock is £1,000, Jan closing stock is £800, Feb Closing Stock is £1,300 and March Closing stock is £1,900. Etc Etc
All my dates are 1st of month but represent data for whole month.
Each year will have a Brought forward amount marked as true in January (BF above). Then movements for Jan - Dec.
Closing balance is fairly straight forward with a time slicer showing YTD (we operate calendar year) since this is just the YTD dax calc, all good.
But how would syntax look for:
1. April closing balance when only Feb-Apr selected in Slicer? I guess dax question here is how to compute a YTD number given a single month/date as a time point.
2. How would we calculate Average stock for YTD, Last 3 Months, Last 6 Months, Last 12 Months. or avge stock across period selected in a slicer.
I do have access and ability to change the underlying database and could generate a CF balance for each date via a stored procedue before loading the cube; but it would be preferable to work with tht data above.
I apologise but as a newbie I have no idea where to start with this one. So I can't upload any suggestions that have gone wrong!
Help and advise gratefully appreciated.
Thanks
Pete
8 Replies
- Greg_Deckler
Community Champion
See if this Quick Measure helps:
- Nice references to YTD etc but not sure how this addresses my issues.
- Ashish_Mathur
Super User
Hi,
- Create a Calendar Table and extract the year: YEAR=YEAR(Calendar[Date]) and Month=FORMAT(Calendar[Date],"mmmm")
- Create a relationship from the Date column of the base data table to the Date column of the Calendar Table
- Create a slicer for Year and Month (Drag Year and Month from the Calendar Table). In the year, select any single year and in the month slicer, select a group of consecutive months, say Feb,March and April
- Write this measure to compute the Closing Balance
=CALCULATE(SUM(Data[Amount]),DATESBETWEEN(Calendar[Date],MINX(ALL(Calendar),Calendar[Date]),MAX(Calendar[Date])))
Does this work?
- Ashish_Mathur
Thanks for tip But the formula returns the Sum of the amounts across the whole data table.
Whilst not solving my problem the combination the tip on using minimum dates and the ALL function might get me a bit closer.
So thanks for help
One of the things I'm trying to do is get a YTD balance when the time slicer is filtered to current month or a YTD for last month of a slicer. To compute this I need to look at the unsliced months (eg June in Slicer and the formula calculate YTD for June). If the dax could compute YTD for any month then is could then be averaged quite easily (I think).
Thanks for assist tho. As a newbie it is interesting to see how these sorts of things are tackled.
Hi All
I've managed to solve point 1 of my questions!!!
To calculate YTD to a single month apply the following:
YTDToAMonth:= CALCULATE(Sum(NLFact[Amount]),DATESYTD(NLFact[MADate]),all(myDates))
Where my dates is a calendar table. Then in Excle cubevalue function use MADate as a filter.
Since this will produce an end balance for each month, I then tried to average the measure by summing it and dividing by the number of months. Two issues here:
1. The measure YTDToAMonth won't sum; it doesn't show in the intellisense at all.
2. Not sure about distincts here but feel I may need them??
If someone could offer inspiration for next step, that would be cool
ArynaPost Partisan
You might find this Profit & Loss dashboard template useful as a reference: https://vidi-corp.com/profit-and-loss-dashboards/
It’s a practical example of financial reporting in Power BI and can help you get moving faster without having to build every part from the ground up.
Hope this helps!
