Forum Discussion

Elliott's avatar
Elliott
Advocate II
10 years ago
Solved

Calculate Difference from Previous 'Date' Dynamically

Hi All,   I am being faced with a new challenge on how to display a graph that purely shows the changes from one 'Date' to the next, in my case this is week by week, as can be seen by a very simpli...
Need Help
  • austinsense's avatar
    austinsense
    10 years ago

    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.

  • austinsense's avatar
    austinsense
    10 years ago

    No that makes sense - should work fine, here's what you do.

     

    Option 1

    1. Undo the sorting and indexing.
    2. Right click your query and hit duplicate.
    3. 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.
    1. Right click the new query and uncheck the "Enable Load" option
    2. 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.
    3. Expand the new column to show the index column.
  • austinsense's avatar
    austinsense
    10 years ago

    All measures - the last one gives us the right number for the grand total (i hope that's what it does!)

  • Sean's avatar
    Sean
    10 years ago

    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...

     

OscJara's avatar
OscJara
New Member
10 years ago

thanks austinsense, I am following your instrucions but I can't find the way to agregate the index.

 

I am trying to do a running total using this code:

ACC AVGPoints = 
CALCULATE(
	SUM(TResponses[AVG Points]),
	FILTER(
		ALL(TResponses[Date of Activity - Valida]),
		TResponses[Date of Activity - Valida] <= MAX(TResponses[Date of Activity - Valida]))
)

 

I can plot the accumulation of AVGPoints using Date as X Axis (top chart with lines), but I also need to plot the accumulation of AVGPoints by weeks (below char with bars).

but the sum its only taking place week by week and not running the total. What should I do?

  • Sean's avatar
    Sean
    Community Champion
    10 years ago
    Look at his response where it says Option 1 and Option 2.

    You create the Index in the Query Editor then use it to do your running total.