Adding Switch Results to Matrix (or different solution)

[Mitchell92]

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.

[DataInsights]

@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

[Mitchell92]

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.

[DataInsights]

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

[Mitchell92]

Hi @DataInsights ,

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

Hopefully you can assist.

Thanks,

Mitch

[DataInsights]

@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