Displaying Aged Debt over two financial years - Function 'IF' scalar expressions have to be Aggregat

[PaulNearney]

Pbix with full repro here

Background

I'm trying to create a stacked chart showing Debt, grouped up by the age of the debt, over two financial years (this year, last year), like this:

Here's my Model:

The "Financial Years" many-many allows me to use a slicer that lets the user select a single financial year, but in the background, it filters to 2 years (the selected FY and the previous FY).

The debt calculation itself needs to expand the evaluation context to ALL years (up to the month that's selected in the month slicer), as the debt itself could go back a number of years.

AgedDebtBands is the table that defines the age bands to use for the debt. My approach to age-banding the debt is loosely based on the approach here https://www.youtube.com/watch?v=ehRqUlcg620

The measure I use to get the debt ageing is:

AgedDebt = 
VAR selectedDate = [End of selected month]
VAR bandStart = MAX(AgedDebtBands[BandStart])
var bandEnd = MAX(AgedDebtBands[BandEnd])

RETURN
	SUMX(
		CALCULATETABLE(
			GROUPBY(
				Debt,
				[InvoiceId], 
				[InvoiceAmount],
				[InvoiceDate],
				"Payment Amount", SUMX(CURRENTGROUP(), IF(Debt[PaymentDate] <= selectedDate, Debt[PaymentAmount], 0))),
			ALL('Financial Years'),
			ALL(Dates[Fiscal Month]),
			Debt[InvoiceDate] <= selectedDate,
			DATEDIFF( Debt[InvoiceDate], selectedDate, DAY) >= bandStart,
			DATEDIFF( Debt[InvoiceDate], selectedDate, DAY) <= bandEnd),
		[InvoiceAmount] - [Payment Amount])

The problem

I can drag the measure (Value) and the "Band" column from AgedDebtBands (Legend) into a table or a stacked chart, and I get aged debt for the current year - this part works perfectly.

However, when I include "Fiscal Year" from Dates into the chart (axis), I get the following error:

MdxScript(Model) (11, 13) Calculation error in measure 'Test Measures'[AgedDebt]: Function 'IF' scalar expressions have to be Aggregation functions over CurrentGroup(). The expression of each Aggregation has to be either a constant or directly reference the columns in CurrentGroup().

I've spent a couple of days at this trying various different methods to get this working, and always get close, but never quite there. Any help on this is greatly appreciated!

[Anonymous]

Hi PaulNearney,

Have you been able to solve this? I'm trying to achieve exactly the same goal, using a similar method. 

Thanks for your response. 

[v-yuta-msft]

@PaulNearney,

This issue may caused by "selectedDate" connected to the date table, how about use summarize or addcolumns instead?

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.