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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.