DAX Subtotals Profit and Loss statement

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,386
I'm using the following method from Imke to produce a nicely formatted pivot table in Excel: https://www.thebiccountant.com/2016...ount-scheme-reports-power-bi-power-pivot-dax/

However, I am building on this to produce projected numbers.

For each reporting heading the user can choose to use a YTD average for the remaining periods or the latest month for each of the remaining periods.

The first measure is the same as Imke's post:
Amt_ :=
CALCULATE ( [Amt], AccountsAllocation )



The next measure uses the selected projection method to work out how much is to be applied to each future period:
BasisMonth :=
IF (
MIN ( ReportsAccountsLayout[Level] ) > 0,
BLANK (),

IF (
[pBasis] = "YTD Average",
[Amt_]
/ (
MAX ( CurrentPeriod[CurrentPeriod] )
- TRUNC ( MAX ( CurrentPeriod[CurrentPeriod] ) / 100 ) * 100
),
CALCULATE (
[Amt_],
FactActuals[Period] = VALUES ( CurrentPeriod[CurrentPeriod] )
)
)
)



For October CurrentPeriod[CurrentPeriod] would be 202007. The financial year starts in April.

And finally, the basismonth is multiplied by the number of remaining periods:
ProjectedActual :=
[BasisMonth]
* (
12
- (
MAX ( CurrentPeriod[CurrentPeriod] )
- TRUNC ( MAX ( CurrentPeriod[CurrentPeriod] ) / 100 ) * 100
)
)



At the moment subtotals are returning blanks (Bold in BasisMonth measure above) because I have no idea how to return the correct result.

Is anyone able to help?
 
Last edited:

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
I went back to the drawing board and had a little think and I think I'm making it more complicated than I need to.

I'm currently trying to apply different formulas in the same measure and am trying to ask the subtotal to deal with this.

Instead what I think I should do is create three measures.

YTDMeasure - that calculates the YTD average or returns null if the Basis is montly
MonthlyMeasure - that calculates the current month or returns null if the basis is YTD
BasisMeasure - that sums the two above.

Which hopefully means the two different projection methods would propagate through to the subtotal..?!?!??11
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top