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 have 2 columns I am attempting to divide, for simplicity, I'll just call them A and B. 
I have another date column with year in it. Year is very simple, it has 2021 through 2018. 

What I'm trying to do in pseudocode is add up everything from A and B for only 2020-2018 and then divide A by B. 

Stuff I have tried: 

  • I have tried CALCULATE, and this works great when I do it for 1 year, but not all 3. 
  • My years need to be dynamic, so I have been using YEAR(NOW())-1, -2, and -3 for 2020-2018, respectively. 
  • I have tried using FILTER in the 2nd part of calculate along with && to get 3 years in, but that didn't work. 

I'm kinda lost at this point. Normally when I do a CALCULATE its on a single filter, or multiple filters from multiple places, not the same filter with different choices. 

Let me know if I can help by giving any additional information. I appreciate any guidence on this, thank you! 

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

     

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft 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!