cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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.

Exam LY.PNG

 

 

 

 

 

Thanks!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Last Year compared to Current YTD

Hi @alison_mergaman,

 

Based on my tests, the formula below should work in your scenario. Smiley Happy

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

View solution in original post

5 REPLIES 5
Highlighted
Microsoft
Microsoft

Re: Last Year compared to Current YTD

Hi @alison_mergaman,

 

Could you try the formula below to see if it works in your scenario? Smiley Happy

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

 

Regards

Highlighted
Frequent Visitor

Re: Last Year compared to Current YTD

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.

Highlighted
Microsoft
Microsoft

Re: Last Year compared to Current YTD

Hi @alison_mergaman,

 

Based on my tests, the formula below should work in your scenario. Smiley Happy

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

View solution in original post

Highlighted
Frequent Visitor

Re: Last Year compared to Current YTD

@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. 

YoY.png

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!

Highlighted
Frequent Visitor

Re: Last Year compared to Current YTD

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (925)