Forum Discussion

alison_mergaman's avatar
alison_mergaman
Frequent Visitor
9 years ago
Solved

Last Year compared to Current YTD

I am attempting to calculate Last year compared to the current year to date. So for my data, I have information as current as of August 2nd, 2017. I want a comparison for that date in 2016. I was abl...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi alison_mergaman,

     

    Based on my tests, the formula below should work in your scenario. :smileyhappy:

    Exams LY =
VAR maxDate =
    LASTDATE ( v_Master_Item_Detail[TransactionDate] )
VAR minDate =
    FIRSTDATE ( v_Master_Item_Detail[TransactionDate] )
RETURN
    CALCULATE (
        [Exams],
        FILTER (
            ALL ( v_Master_Item_Detail ),
            v_Master_Item_Detail[TransactionDate] <= DATEADD ( maxDate, -1, YEAR )
                && v_Master_Item_Detail[TransactionDate] >= DATEADD ( minDate, -1, YEAR )
        )
    )

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi alison_mergaman,

 

Based on my tests, the formula below should work in your scenario. :smileyhappy:

Exams LY =
VAR maxDate =
    LASTDATE ( v_Master_Item_Detail[TransactionDate] )
VAR minDate =
    FIRSTDATE ( v_Master_Item_Detail[TransactionDate] )
RETURN
    CALCULATE (
        [Exams],
        FILTER (
            ALL ( v_Master_Item_Detail ),
            v_Master_Item_Detail[TransactionDate] <= DATEADD ( maxDate, -1, YEAR )
                && v_Master_Item_Detail[TransactionDate] >= DATEADD ( minDate, -1, YEAR )
        )
    )

 

Regards

alison_mergaman's avatar
alison_mergaman
Frequent Visitor
9 years ago

v-ljerr-msft Please ignore my last message!!! This code worked!!! I had to use ALLEXCEPT because I have filters for my report. Thank you so much!!!!