cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PaulNearney
Frequent Visitor

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

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:

 

goal.png

 

Here's my Model:

 

Model.png

 

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!

 

1 REPLY 1
v-yuta-msft
Community Support
Community Support

@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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.