Doing division based on multiple years

[Anonymous]

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! 

[mahoneypat · Accepted solution]

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]

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!