Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Doing division based on multiple years

Hello all, I'm running into what I thought was a simple issue, but its turned into a multiple hours event of my DAX code not working. I'll try to keep it simple in what I'm attempting to do.  I ha...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    Have you tried a measure expression like this?  This should work in a card.  If you use it in a table/matrix/other visual, you may need to add other terms to the CALCULATE.

     

    A divided by B Prev 3 yrs =
    VAR thisyear =
        YEAR ( TODAY () )
    VAR result =
        CALCULATE (
            DIVIDE ( SUM ( Table[A] ), SUM ( Table[B] ) ),
            Table[Year] >= thisyear - 3
                && Table[Year] <= thisyear - 1
        )
    RETURN
        result

     

    Pat

     

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

Have you tried a measure expression like this?  This should work in a card.  If you use it in a table/matrix/other visual, you may need to add other terms to the CALCULATE.

 

A divided by B Prev 3 yrs =
VAR thisyear =
    YEAR ( TODAY () )
VAR result =
    CALCULATE (
        DIVIDE ( SUM ( Table[A] ), SUM ( Table[B] ) ),
        Table[Year] >= thisyear - 3
            && Table[Year] <= thisyear - 1
    )
RETURN
    result

 

Pat

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thank you mahoneypat 

    This logic worked well enough to allow me to continue to troubleshoot further. I am indeed using this in a table. The result returns slightly skrewed numbers than I would expect, but this isn't a problem with the DAX logic. 

    So close, yet so far!