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,

 

Could you try the formula below to see if it works in your scenario? :smileyhappy:

Exams LY =
CALCULATE (
    TOTALYTD ( [Exams], v_Master_Item_Detail[TransactionDate] ),
    SAMEPERIODLASTYEAR ( v_Master_Item_Detail[TransactionDate] )
)

 

Regards

alison_mergaman's avatar
alison_mergaman
Frequent Visitor
9 years ago

Hi v-ljerr-msft When I used that calculation it included all of 2016's numbers when 2016 is Exam LY. I just want Exam LY to go up to the Exams this year current date. So if we are looking at Exam This year = August 2, 2017, I want Exam LY to be cut off at August 2nd, 2016.

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

    • alison_mergaman's avatar
      alison_mergaman
      Frequent Visitor
      9 years ago

      v-ljerr-msft First I want to thank you so much for all of your efforts! But sadly, this equation does not work for me either. I took a screen shot showing from Feb 2016- April 2017 comparing that years monthly data, the last year data I originally created and the one you just created. This time your last year data numbers changed slightly. You can tell those numbers are wrong when looking at the second column and and seeing the number in Exams Only LY. 

      So here March 2016: 20,416 and March 2017's LY: 20416 but your March 2017's LY: 20756. I know this is incredibly weird. Thank you again for al of your effort to helping me solve this issue!