Forum Discussion
Adding Switch Results to Matrix (or different solution)
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
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.
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.
Hi DataInsights ,
PBIX link here: TEST - Debtors Point in Time2.pbix
Hopefully you can assist.
Thanks,
Mitch
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