cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Elliott
Advocate II
Advocate II

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 simplified data source here (Has been simplified just to show structure of data):

 

Capture2.PNG

 

What I would like to do is create a graph like as can be seen below:

 

Capture.PNG

I would like it to simply show the value difference from the previous week. So for instance, I would like week WK03 to show as and WK04 / WK05 to show as 0 etc.

 

I also have multiple slicers on my page to drill down based on specifc measures, so the total value will always change, hence my solution needs to be dynamic.

 

I believe it's going to need something along the lines of.... WK'X' VALUE - WK'Y' VALUE = WK'Y' Difference

Then the slicers will all fall into place as the same calculation will be running on only selected rows..?

 

Need some help putting that into DAX though! Smiley Happy

 

Thanks

Elliott

4 ACCEPTED SOLUTIONS
austinsense
Impactful Individual
Impactful Individual

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.

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂

View solution in original post

austinsense
Impactful Individual
Impactful Individual

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.
Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂

View solution in original post

austinsense
Impactful Individual
Impactful Individual

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

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂

View solution in original post

Sean
Community Champion
Community Champion

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

 

PoP Calculations3.png

View solution in original post

13 REPLIES 13
v-mochri
New Member

Hello All,

I have the same issue, i'm trying to calculate dynamicly the different of the revenue between the fiscal month selected by the user and the previous month of selected month.

Please find below an example of what i want to do:

New_Measure=SUMX(FILTER(MyTable;Month=Selected Month);MyMeasure)-SUMX(FILTER(MyTable;Month=Previous Month);MyMeasure)

I have a table time sorted by Month with an id for each month as below:

  

ID Fiscal Month Month Name

1July, 2016July
2August, 2016August
3September, 2016September

 

Thank you for your help.

Simo

OscJara
New Member

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

ACC AVGPoints by weeks.pngbut the sum its only taking place week by week and not running the total. What should I do?

Sean
Community Champion
Community Champion

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.
austinsense
Impactful Individual
Impactful Individual

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.

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂

View solution in original post

Sean
Community Champion
Community Champion

@austinsense these are all measures right?

 

What does the last one do?

Total - PoP Change - Aggregates Correctly = SUMX(Table, [Total - PoP Change] )

PoP Calculations.png

 

austinsense
Impactful Individual
Impactful Individual

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

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂

View solution in original post

Eventually got my head round out it!

 

Solution works perfectly, thanks for your detailed descriptions

 

Picture1.png

 

Thanks!

Thanks for your solution,

 

However I did think I included this in original description but looks like I missed it out,

 

As it was a simplified table, I missed out the fact that there are several different customers also included in the table in a separate column. Therefore there for multiple occurrences of the same week for instance, therefore my data would actually displayed more like the below screenshot after applying the sort and indexing: (Before and after sorting by Week and adding index)

 

Picture1.pngPicture2.png

 

Also, ontop of that, there is another column for sub categories of customer, so there could be for instance 2 rows for WK01 Customer A (each sub category).

 

Would your solution also work with this format of data as it would be looking at index 0 and 1 for instance, which is 2 individual customers but same week.

 

Sorry if I'm not making much sense!

 

Thank again

austinsense
Impactful Individual
Impactful Individual

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.
Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂

View solution in original post

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

 

Picture1.pngPicture2.png

 

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 Smiley Happy

 

austinsense
Impactful Individual
Impactful Individual

Instead of doing ALL(Table), try ALL( Table[Index] ) - you want it to ignore the week filter but keep the customer filter

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂
Sean
Community Champion
Community Champion

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

 

PoP Calculations3.png

View solution in original post

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

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors