This topic contains 11 replies, has 3 voices, and was last updated by 
-
Posts
-
I’ve got a set of data I am reporting on, and need to show how much the value changed between the last two dates in the set. This is a rolling 52 week data set (each week the oldest is dropped, and a new week added). I show the data in a line chart, and have a cell that displays “1 week change).
The basic logic is (MAX DATE VALUE) – (MAX DATE MINUS 1 VALUE) = CHANGE
In the attached you’ll see an example data set where I have it worked out with basic Excel formulas. Let me know how you would approach.
Workbook didn’t upload, so here it is.
One more time…
Hi Porter,
you can create two measures:
First is to calculate the total sales and you already did this in your workbook Sales Revenue:=SUM(Sales[Sales]).
Second is to calculate the total sales minus one interval which is days at your case Sales Revenue minus one day:=CALCULATE(SUM(Sales[Sales]),DATEADD(Sales[Date],-1,DAY))
your requested measure is only calculating the difference
sales difference := Sales Revenue – Sales Revenue minus one day
hope this helps.
Thank you
I appreciate the response, but this doesn’t give me what I need. I apologize, in the example I posted it was a continuous set of dates. In the actual data the totals are a week apart, that could be 7 days or maybe 6 days or 8 days if there is a holiday etc. What I really need is the difference between the 2 most recent dates, and only that value. I don’t need it for every date in the set, just the difference between last 2.
Again, I apologize if I made this confusing.
No worries , I think the logic still holds , I have deleted one day from the last date , so you will have one day apart between 19/07/2016 and 17/07/2016.
when you drag the measure difference you will get the difference between the last two dates. I have attached is the workbook.
Hope this helps.
Thank you
In the file you sent back, sales on July 19th are 513, which is also the amount the difference calculated. I think this is because there is no value for July 18th.
What it should show is the difference between sales on July 17 which are 860 and July 19 which are 513. That is -347
Sorry thought you wanted the difference between totals, any way here is what you need
Sales Revenue:=CALCULATE(SUM(Sales[Sales]),LASTDATE(Sales[Date]))
sales minus 1 day:=CALCULATE(SUM(Sales[Sales]),LASTDATE(DATEADD(Sales[Date],-1,DAY)))
difference:=[Sales Revenue]-[sales minus 1 day]
The result is -347
attached is the workbook.
Scott,
Getting the last sales date of a set is relatively easy. The tricky part is how to get the date for the prior sales date, regardless of possible dates between that where there may have been no sales due to holiday or temporary business closure.
I can think of two possible ways: one totally through measures and the other by adding a calculated column to the calendar table that determines the prior day of sales activity.
Please see attached workbook that demonstrates both ways of getting the “prior” sales date.
On the worksheet with the pivot I removed the VLOOKUP and used CUBEVALUE functions to extract the appropriate measures from the data model. Also, please notice that I removed July 18 from the sales table source to test the results.
I like the measures approach, I think that is the more efficient way to handle. Thank you for both solutions! Great learning opportunity.
As I reviewed these I thought of two other potential possibilities using RANKX to rank the dates and then harvest the two largest values, or TOPN may be another approach. Then maybe using CUBESET to pull the dates and CUBEMEMBER to display the sales values for each. (I think I saw something like that in the online training course). Not sure how to write either of these…
Scott,
I like your idea of using TOPN.
To get the ‘sales date before the last sales date’ you could replace the current formula with:
Sales Day Before Last Sales Date:=MINX ( TOPN ( 2, FILTER ( ADDCOLUMNS ( ALL ( 'Calendar' ), "Has Sales ", IF ( COUNTROWS ( RELATEDTABLE ( Sales ) ) > 0, 1, 0 ) ), [Has Sales] = 1 ), 'Calendar'[Date], DESC ), Calendar[Date] )Definitely more elegant than my original formula. The FILTER gets all calendar dates that have sales, the TOPN( 2,…,DESC) gets the last two dates and MINX gets the earlier of the two calendar dates.
There is one special case that should be considered: what is returned if there is only one calendar date that has sales? The answer is: the same date as the last, which may be what one may want to use in your “Change Between Last Two Dates” because the result will be 0 and not negative. This scenario may be a moot point because you already have more than one date of sales in your model.
Appreciated everyone’s input on this topic. For Scott who posted this challenging case, for Fahad who had the courage to tackle it first, and also for the feedback that made the solution better. Exchanges like this is what the forum should be about more often.
See attached workbook with new measure.
Tom
AWESOME!!!!!
The forum ‘Power Pivot’ is closed to new topics and replies.