topic Re: Adding Switch Results to Matrix (or different solution) in DAX Commands and Tips

Adding Switch Results to Matrix (or different solution)

Mitchell92 — Wed, 22 May 2024 00:48:57 GMT

Hi all,

I'm hoping someone may be able to assist.

I have a list of invoice #'s with invoice date and amount, and also a calendar table with a relationship between the invoice date and calendar date.

On the report, I want to be able to select the calendar date using a slicer, and have a table populate the age of the invoice (per selected date), and summarise the invoice amounts by an "age bracket" within a matrix.

I have achieved two things thus far:

1) I have been able to calculate the invoice age per selected slicer date using the following measure:

Age2 =

VAR _RangeStart = MIN(AR[TRXDTE])

VAR _RangeEnd = MAX(DateTable[Date])

RETURN

DATEDIFF(_RangeStart,_RangeEnd,DAY)

2) I have created a measure with a switch formula to distribute ages between age brackets:

AgeBrackets = switch( true,

[Age2] <= 30, "0 - 30", [Age2] <= 60, "31 - 60", [Age2] <= 90, "61 - 90", [Age2] < 120, "91 - 120","120+")

What I am unable to achieve is putting the AgeBrackets measure into a matrix as the column headers (with invoice value as values) to summarise this effectively.

I'm hoping someone has experience with this / may be able to assist? Hoping its a quick and easy solution.

PBIX LINK:

PBIX

Thanks in advance.

Re: Adding Switch Results to Matrix (or different solution)

DataInsights — Wed, 22 May 2024 13:12:06 GMT

Mitchell92,

Create a disconnected table like the one below (no relationships). Sort Age Bracket by Age Bracket Index.

Create measures:

Sum of Total = SUM ( ARITEMA2[Total] )

AgeBrackets = VAR vLowerBound = SELECTEDVALUE ( AgeBracket[Lower Bound] ) VAR vUpperBound = SELECTEDVALUE ( AgeBracket[Upper Bound] ) VAR vAge = [Age2] VAR vResult = IF ( vAge >= vLowerBound && vAge <= vUpperBound, [Sum of Total] ) RETURN vResult

Add AgeBracket[Age Bracket] to matrix columns, and [AgeBrackets] to matrix values. You can expand this example to handle totals with a measure like this:

AgeBrackets = VAR vLowerBound = SELECTEDVALUE ( AgeBracket[Lower Bound] ) VAR vUpperBound = SELECTEDVALUE ( AgeBracket[Upper Bound] ) VAR vAge = [Age2] VAR vResult = SUMX ( VALUES ( HEADER[OPS] ), IF ( vAge >= vLowerBound && vAge <= vUpperBound, [Sum of Total] ) ) RETURN vResult

Re: Adding Switch Results to Matrix (or different solution)

Mitchell92 — Wed, 22 May 2024 22:59:47 GMT

Hi DataInsights , thank you for your assistance. This has certainly got me a lot further - I have one issue though. On my actual data set, theres about 4 years worth of invoicing. This solution seems to be lumping everything into the max bracket within the category (everything is being categorised as 120+).

Wondering if there is a quick solution for that?

Thanks in advance.

Re: Adding Switch Results to Matrix (or different solution)

DataInsights — Tue, 18 Jun 2024 12:57:50 GMT

Mitchell92,

Would you be able to provide an example of data that appears in the wrong category? You could add additional rows to the shared pbix if that's easier.

Re: Adding Switch Results to Matrix (or different solution)

Mitchell92 — Tue, 18 Jun 2024 22:42:58 GMT

Hi DataInsights ,

PBIX link here: TEST - Debtors Point in Time2.pbix

Hopefully you can assist.

Thanks,

Mitch

Re: Adding Switch Results to Matrix (or different solution)

DataInsights — Tue, 18 Jun 2024 23:26:26 GMT

Mitchell92,

Try this measure:

AgeBrackets = VAR vLowerBound = SELECTEDVALUE ( AgeBracket[Lower Bound] ) VAR vUpperBound = SELECTEDVALUE ( AgeBracket[Upper Bound] ) VAR vMaxDate = MAX ( DateTable[Date] ) VAR vResult = SUMX ( Invoicing, VAR vInvDate = Invoicing[Date] VAR vAge = DATEDIFF ( vInvDate, vMaxDate, DAY ) RETURN IF ( vAge >= vLowerBound && vAge <= vUpperBound, [Sum of Total] ) ) RETURN vResult