cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
srik Regular Visitor
Regular Visitor

Calculate latest date and amount to it and same date last year for comparison

I have this dataset where I need to identify the latest date available which in my case if 4/12/17 and calculate corresponding amount which is 900. So the way I'm doing it is calculate LatestDay =MAX(date) and using this calculation in my measure.I need to compare this value to last year same day(4/12/16) amount which is 1150.

 

But LatestDay:Amount is not working as expected, instead it is giving me whole April 2017 month amount(17400). So not sure what is going wrong.

 

LatestDay:Amount = CALCULATE(SUM('Table'[Amount]), FILTER('Table', 'Table'[date] = [LatestDay]))

 

PreviousYearSameDay:Amount = CALCULATE([LatestDay], SAMEPERIODLASTYEAR('Table'[date]))

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Calculate latest date and amount to it and same date last year for comparison

@srik

 

Check this PBIX using the formulas:

 

https://drive.google.com/file/d/0B95C8CKdTZE3RGdPMENFUXhTMG8/view?usp=sharing

 

Tot.png

 

 




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




9 REPLIES 9
srik Regular Visitor
Regular Visitor

Re: Calculate latest date and amount to it and same date last year for comparison

Pasting data here as I cant find an option to attach.

 

date,ProdType,Amount,Quantity,year
4/1/2016 0:00,Bikes,950,2,2016
4/1/2016 0:00,Clothing,1000,2,2016
4/1/2016 0:00,Parts,800,4,2016
4/2/2016 0:00,Bikes,250,10,2016
4/2/2016 0:00,Parts,1000,6,2016
4/3/2016 0:00,Bikes,400,12,2016
4/3/2016 0:00,Parts,250,7,2016
4/4/2016 0:00,Bikes,400,14,2016
4/4/2016 0:00,Clothing,200,6,2016
4/4/2016 0:00,Parts,250,9,2016
4/5/2016 0:00,Bikes,700,8,2016
4/5/2016 0:00,Clothing,500,5,2016
4/5/2016 0:00,Parts,550,10,2016
4/6/2016 0:00,Bikes,50,5,2016
4/6/2016 0:00,Clothing,1000,5,2016
4/6/2016 0:00,Parts,300,15,2016
4/7/2016 0:00,Bikes,450,8,2016
4/7/2016 0:00,Clothing,350,10,2016
4/7/2016 0:00,Parts,150,17,2016
4/8/2016 0:00,Bikes,600,4,2016
4/8/2016 0:00,Clothing,1150,4,2016
4/8/2016 0:00,Parts,1050,20,2016
4/9/2016 0:00,Bikes,1500,5,2016
4/9/2016 0:00,Parts,600,2,2016
4/10/2016 0:00,Bikes,1050,12,2016
4/10/2016 0:00,Parts,400,5,2016
4/11/2016 0:00,Bikes,400,5,2016
4/11/2016 0:00,Parts,700,11,2016
4/12/2016 0:00,Bikes,350,7,2016
4/12/2016 0:00,Clothing,100,3,2016
4/12/2016 0:00,Parts,700,23,2016
4/13/2016 0:00,Bikes,550,30,2016
4/13/2016 0:00,Clothing,1000,21,2016
4/13/2016 0:00,Parts,950,14,2016
4/14/2016 0:00,Bikes,850,14,2016
4/14/2016 0:00,Clothing,1000,19,2016
4/14/2016 0:00,Parts,750,20,2016
4/15/2016 0:00,Bikes,850,17,2016
4/15/2016 0:00,Parts,800,15,2016
4/16/2016 0:00,Bikes,750,9,2016
4/16/2016 0:00,Parts,250,2,2016
4/17/2016 0:00,Bikes,200,12,2016
4/17/2016 0:00,Parts,1000,5,2016
4/18/2016 0:00,Bikes,500,5,2016
4/18/2016 0:00,Clothing,550,11,2016
4/18/2016 0:00,Parts,50,6,2016
4/19/2016 0:00,Bikes,1000,3,2016
4/19/2016 0:00,Clothing,300,23,2016
4/19/2016 0:00,Parts,450,30,2016
4/20/2016 0:00,Bikes,350,21,2016
4/20/2016 0:00,Clothing,150,14,2016
4/20/2016 0:00,Parts,600,14,2016
4/21/2016 0:00,Bikes,1150,19,2016
4/21/2016 0:00,Parts,1050,20,2016
4/22/2016 0:00,Bikes,1500,17,2016
4/22/2016 0:00,Clothing,600,15,2016
4/22/2016 0:00,Parts,1050,2,2016
4/23/2016 0:00,Bikes,400,12,2016
4/23/2016 0:00,Parts,400,5,2016
4/24/2016 0:00,Bikes,700,5,2016
4/24/2016 0:00,Parts,50,11,2016
4/25/2016 0:00,Bikes,100,6,2016
4/25/2016 0:00,Parts,700,3,2016
4/26/2016 0:00,Bikes,550,23,2016
4/26/2016 0:00,Clothing,1000,30,2016
4/26/2016 0:00,Parts,950,21,2016
4/27/2016 0:00,Bikes,850,14,2016
4/27/2016 0:00,Clothing,1000,14,2016
4/27/2016 0:00,Parts,750,19,2016
4/28/2016 0:00,Bikes,850,20,2016
4/28/2016 0:00,Clothing,800,17,2016
4/28/2016 0:00,Parts,750,15,2016
4/29/2016 0:00,Bikes,250,2,2016
4/29/2016 0:00,Clothing,30,10,2016
4/29/2016 0:00,Parts,900,12,2016
4/30/2016 0:00,Bikes,950,14,2016
4/30/2016 0:00,Parts,850,8,2016
4/1/2017 0:00,Bikes,1000,11,2017
4/1/2017 0:00,Clothing,750,1,2017
4/1/2017 0:00,Parts,850,2,2017
4/2/2017 0:00,Bikes,800,12,2017
4/2/2017 0:00,Parts,750,5,2017
4/3/2017 0:00,Bikes,250,5,2017
4/3/2017 0:00,Parts,240,11,2017
4/4/2017 0:00,Bikes,1000,6,2017
4/4/2017 0:00,Clothing,10,3,2017
4/4/2017 0:00,Parts,1200,23,2017
4/5/2017 0:00,Bikes,750,30,2017
4/5/2017 0:00,Clothing,850,21,2017
4/5/2017 0:00,Parts,800,14,2017
4/6/2017 0:00,Bikes,750,14,2017
4/6/2017 0:00,Parts,250,19,2017
4/7/2017 0:00,Bikes,200,20,2017
4/7/2017 0:00,Parts,1000,17,2017
4/8/2017 0:00,Bikes,800,15,2017
4/8/2017 0:00,Parts,750,2,2017
4/9/2017 0:00,Bikes,250,15,2017
4/9/2017 0:00,Parts,200,4,2017
4/10/2017 0:00,Bikes,1000,5,2017
4/10/2017 0:00,Parts,500,10,2017
4/11/2017 0:00,Bikes,800,4,2017
4/11/2017 0:00,Parts,750,3,2017
4/12/2017 0:00,Bikes,700,20,2017
4/12/2017 0:00,Parts,200,4,2017

Super User
Super User

Re: Calculate latest date and amount to it and same date last year for comparison

@srik

 

Hi, try with this measures:

 

 

SumLastDate = CALCULATE(Sum(Table1[Amount]),LASTDATE(Table1[date]))

 

SumLastDate-1Year = CALCULATE(Sum(Table1[Amount]),DATEADD(LASTDATE(Table1[date]),-1,YEAR))

 

 




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




srik Regular Visitor
Regular Visitor

Re: Calculate latest date and amount to it and same date last year for comparison

I already tried LASTDATE measure it would be get me the latest date(4/12/2017) but when I use it in filter in measure calculation it wont work, it would still give me whole april amount.

Super User
Super User

Re: Calculate latest date and amount to it and same date last year for comparison

@srik

 

Check this PBIX using the formulas:

 

https://drive.google.com/file/d/0B95C8CKdTZE3RGdPMENFUXhTMG8/view?usp=sharing

 

Tot.png

 

 




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




srik Regular Visitor
Regular Visitor

Re: Calculate latest date and amount to it and same date last year for comparison

What version is the attached powerbi version?

Super User
Super User

Re: Calculate latest date and amount to it and same date last year for comparison

April Version.




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




v-huizhn-msft Super Contributor
Super Contributor

Re: Calculate latest date and amount to it and same date last year for comparison

Hi @srik,

Have you resolved your problem? I also get the correct result like @Vvelarde poted.  Please feel free to ask if you have any other issue. If you have resoved you problem, please mark corresponding reply as answer.

Thanks,
Angelia

mattbrice Senior Member
Senior Member

Re: Calculate latest date and amount to it and same date last year for comparison

You got the whole month of April value ( 17400 ) because of the way you wrote the FILTER function.  I am assuming you have April 2017 either on the rows or selected via a slicer?

 

Looking at your code,  LatestDay =MAX(date) by itself is ok, but you called the measure inside a FILTER.  FILTER is an iterator that creates a row context for every row in "Table", iterating over every value of Table[Date] visible in current filter context.  Your issue is that you called it as a measure [LatestDay] instead of just putting in "FILTER ( Table, Table[Date] = MAX ( Table[Date] )". Measures called by name are implicitly wrapped in a CALCULATE.  CALCULATE causes the currently iterated Table[Date] value to be transitioned from a row context into the filter context.  This causes the measure [LatestDay] to evaluate only the single date and therefore always returns the currently iterated row. (MAX of a single date is that date.)  Therefore the 'Table'[date] = [LatestDay]) test is always true and returns all the dates visible in current filter context, and not just the latest like you are expecting, which is why you got the whole month value. (and why i assume you have April 2017 somehow selected in a slicer)

 

So depending on what you have for row/column/page/report filters, the other suggested code samples should work...just be sure to use @Vvelarde code as written and don't try to use the LASTDATE function inside the FILTER statement or you will end up with the same situation I describe above.  LASTDATE is a table function that triggers context transition...

 

Good luck.


srik Regular Visitor
Regular Visitor

Re: Calculate latest date and amount to it and same date last year for comparison

That worked thanks.