Forum Discussion
Calculate Difference from Previous 'Date' Dynamically
Cool question - using the query editory you can sort the table and add an index so that each row has a distinct number (0,1,2,3, etc). Here's the formula you want to write ... I broke it out into pieces.
Total = SUM(Total) Total - Previous Period = VAR thisperiod = MAX(Index) RETURN CALCULATE ([Total], FILTER (ALL(Table), Index = (thisperiod - 1)) Total - PoP Change = IF( [Total] && [Total - Previous Period], [Total] - [Total - Previous Period] ) Total - PoP Change - Aggregates Correctly = SUMX(Table, [Total - PoP Change] )
With current and previous date calculations we usually use a date table and the date/time functions but those calculations don't work with weeks, so you'd have to do it this way.
No that makes sense - should work fine, here's what you do.
Option 1
- Undo the sorting and indexing.
- Right click your query and hit duplicate.
- In this new query you're going to remove all the columns except the week, then remove the duplicates, sort the column, and add the index.
Option 2 - This is a continuation of Option 1, You can keep two tables (option 1) or put the index back on the original query (option 2), your choice.- Right click the new query and uncheck the "Enable Load" option
- In the old query, select the weeks column and click on the merge queries option up in the ribbon. Merge the old query with the new query based using the week column. Left Join is chosen by default - that's what you want.
- Expand the new column to show the index column.
All measures - the last one gives us the right number for the grand total (i hope that's what it does!)
I have now tested the same solution following your steps for multiple customers (extra column that will be filtered on)
Finding that there is an issue with the formula reading previous date relevant to that filter
What appears to be happening... When there is no filter selected the values show correct, i.e. overall total shows overall difference by Week. No problems.
However, when applying a filter, it appears as though it is picking up the relevant quantity for the customer/week, but when it is trying to take away from the previous week, it is calculating Customer A week 2 / Customer B week 2 / Customer C week 2, then taking Customer C week 1 away for instance.
Example: As you can see from the screenshots above,
Customer A week 1 = 5
Customer B week 1 = 4
Customer C week 1 = 6
Total 15
Therefore, the graph is showing Customer C week 2 (3) - 15 to result in -12
Thanks for your assistance on this :smileyhappy:
Instead of doing ALL(Table), try ALL( Table[Index] ) - you want it to ignore the week filter but keep the customer filter
Elliott Try this...
Total in Previous Period 3 = VAR thisperiod = MAX('Table'[Index]) RETURN CALCULATE ( [Total Measure], ALLEXCEPT('Table', 'Table'[Customer]), 'Table'[Index] = thisperiod - 1)I think these are the results you are looking for...
You're the man!
Exactly the results I was after,
Had a few more columns I wanted to further drill down by after Customer, so simply excluding them as well in your formula works a treat.
Example below:
Total in Previous Period 3 = VAR thisperiod = MAX('Table'[Index]) RETURN CALCULATE ( [Total Measure], ALLEXCEPT('Table', 'Table'[Location], 'Table'[ActiveY/N], 'Table'[Customer]), 'Table'[Index] = thisperiod - 1)Thanks alot