2 Replies Latest reply on Jul 11, 2018 4:59 AM by Ian Armstrong

# Calculate Difference in a Measure's Value Between First Day and Last Day of Period

I'm attempting to make a KPI viz that shows the sales of certain items over time, in particular showing:

1) sparklines showing sales over a two week period and...

2) a conditionally formatted up or down arrow (green up, red down) visualizing whether the value of sales has increased or decreased when comparing the first day of the period and the last day

As you can see in the attached dummy workbook, I know how to accomplish the sparkline part.  It's the arrow part that I can't get.  In particular, I can't figure out a calculation that isolates the sales value for the first day and last day for each item type (so that I can then make a second calculation for the difference between the two, and then create the arrows).

The image at the top is what I'm looking for.

Is this even possible on the same crosstab/viz as the sparklines?  If I need to build the arrows on a separate worksheet and then just float/align them on a dashboard to get the intended effect, that's fine with me -- I just can't figure it out either way.

• ###### 1. Re: Calculate Difference in a Measure's Value Between First Day and Last Day of Period

Try using lookup function to get the first and the last sales value for the dimensions.

LOOKUP(SUM([Sales]),FIRST()) - LOOKUP(SUM([Sales]),LAST())

• ###### 2. Re: Calculate Difference in a Measure's Value Between First Day and Last Day of Period

That did the trick!  You're a savior, thanks!

FYI for anyone that stumbles upon this thread with the same question -- when I used my real data, I had to convert my measure to an aggregate using ATTR.  The formula looked like this:

LOOKUP(ATTR(SUM[Sales])),FIRST()) - LOOKUP(ATTR(SUM[Sales])),LAST())