Forum Discussion

Mitchell92's avatar
Mitchell92
Helper II
2 years ago

Adding Switch Results to Matrix (or different solution)

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 calenda...
Need Help
Mitchell92's avatar
Mitchell92
Helper II
2 years ago

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's avatar
DataInsights
Super User
2 years ago

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.

    • DataInsights's avatar
      DataInsights
      Super User
      2 years ago

      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