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