6 Replies Latest reply on Aug 13, 2018 2:08 PM by teresa.chan.0

# Showing last value for some measure and summing others

Hello,  I have two fields that I am trying to graph on a yearly chart. The two fields are Revenue and Return, both presented on a semi-annual basis in my data source. I'd like to chart  this yearly such that the Revenue is summed up for the year, but to only show the last Q3 value for Return. I've looked at some other entries, and saw LAST() suggested, but not sure how to use this for my data as setting this to a true filter just shows me 2048 data. Here is an example of the data:

Date                             Revenue        Return

 3/1/2019 5,752,543 3.48% 9/1/2019 4,434,341 3.08% 3/1/2020 2,273,033 2.73% 9/1/2020 2,068,339 2.85%

I'd like the chart to show:

2019: Revenue: 10,186,884 (5,752,573+4.434.341), Return: 3.08%
2020: Revenue: 4,341,372 (2,273,033+2,068,339), Return: 2.85

I've attached a copy of my workbook.  Any help or pointers to get me in the right direction would be greatly appreciated!

• ###### 1. Re: Showing last value for some measure and summing others

See attached

• ###### 2. Re: Showing last value for some measure and summing others

What I did:

It really comes down to this calc:  [Return Last].

{ FIXED DATEPART('year', [Date]) : sum(  if {fixed DATEPART('year', [Date]) : MAX([Date])}

= [Date] then [Return] END ) }

There are two parts to this calc.  Buried in the logic is this fixed expression:

{fixed DATEPART('year', [Date]) : MAX([Date])}

It says, for each year, find the largest [Date].

(I built the syntax for "year" by simply dragging the YEAR of the Date pill into the calc editor.)

So inside the SUM logic it says, "for any year, if the [Date] value is the biggest in that year, then grab the [Return] value".

• ###### 3. Re: Showing last value for some measure and summing others

Thank you Joe!

Thank you very much for the example and the explanations, so clever! I tried this and it worked great to show the last value only for the Return series.  One more thing I noticed however, it doesn't seem to respect the filters. The actual dataset has another column called case which tags each of the values as "Falling" or "Rising." The ultimate goal is to be able to compare the Return and Revenue from the Falling case vs the Rising case.   I had been doing this by "Case" as a filter in my other charts, but this Return last calculations seems to add up the two regardless of the filter (perhaps due to the fixed date argument?). Do you have any suggestions on how to modify it so that it respects filters please?

 Date Revenue Return Case TBL Results 3/1/2019 5,752,543 3.48% Rising 9/1/2019 4,434,341 3.08% Rising 10,186,884 3.08% 3/1/2019 4,251,023 1.58% Falling 9/1/2019 4,025,077 1.68% Falling 8,276,100 1.68%

Thanks again!

• ###### 4. Re: Showing last value for some measure and summing others

My revised workbook with your Return Last column is attached, along with the Case fields.