Power Pivot DAX Balance sheet averages

[PeteSmith6730]

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

[PeteSmith6730]

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.

[PeteSmith6730]

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

[Ashish_Mathur]

Hi,

Share your PBI file and show the expected result there.

[Ashish_Mathur]

Hi,

1. Create a Calendar Table and extract the year: YEAR=YEAR(Calendar[Date]) and Month=FORMAT(Calendar[Date],"mmmm")
2. Create a relationship from the Date column of the base data table to the Date column of the Calendar Table
3. 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
4. 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?

[Greg_Deckler]

See if this Quick Measure helps:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[PeteSmith6730]

Nice references to YTD etc but not sure how this addresses my issues.