cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Community Support
Community Support

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

@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
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors