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 able to accomplish this by doing 

 

Exams LY = CALCULATE([Exams], FILTER(SAMEPERIODLASTYEAR(v_Master_Item_Detail[TransactionDate].[Date]), MAX(v_Master_Item_Detail[Day of Year]))).

 

Drilling down the table to Year-Month-Day it works preferctly, but when I drill up to Year-Month August 2016 is giving me the entire Month of the August for 2016 instead of the 2 dates added together. Is there a way to pull the information so that Year-Month August 2016 will actually equal adding together the Days in 2016? Here's the table screen shot for reference. Please let me know if you need anyhting else.

 

 

 

 

 

Thanks!

 

 

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

5 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft 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
        Microsoft 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