Difference Measure between filtered column values in a matrix

Question

Hi,&nbsp;There could be a suggestion for this elsewhere, but I've been searching for a while and not found anything! I pull an export from a system every week, the spreadsheet is named the date of the export and it's saved in a folder. Power BI uses that folder as the data source and the source name is converted into a date within the query.&nbsp;Each row in the export has a unique ID and a value alongside it. I have created a matrix which looks similar to the below:Export Date&nbsp; &nbsp; &nbsp; &nbsp;31st July 2019&nbsp; &nbsp; &nbsp; 31st August 2019&nbsp; &nbsp;30th September 2019&nbsp; &nbsp; 31st October 2019Unique ID m&nbsp; &nbsp; &nbsp; MB Usage&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MB Usage&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;MB Usage&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MB Usage123456&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 205&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;303&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;210&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;300&nbsp;112233&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 201&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;200&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;210&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;211232123&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 101&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;500&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;400534234&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 650&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;300&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;250&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;241&nbsp;I'm wanting to create a measure that will fit in between each column and work out the difference between one date's value and the other.&nbsp;There is actually more data than these included dates because a sheet is exported every week, but I have the visual only look at the last date of each month.&nbsp;Many thanks in advance.

greg_deckler · Answer

So, I would expect that would be something along the lines of:
&nbsp;
Measure 1 = CALCULATE([Measure],PREVIOUSMONTH('Dates'[Date]))
&nbsp;
Or you could brute force it similar to:
&nbsp;
Measure 1 =
  VAR __Date = MAX('Table'[Date])
  VAR __Year = YEAR(__Date)
  VAR __Month = MONTH(__Date)
  VAR __Day = DAY(__Date)
  VAR __PreviousDate = 
    IF(__Month = 1,DATE(__Year - 1,12,__Day),DATE(__Year,__Month-1,__DAy))
RETURN
  CALCULATE([Measure],'Table'[Date] = __PreviousDate)
&nbsp;

euankonnektis · Answer

Hi Greg,&nbsp;Thanks for your quick response. Have I understood you correctly by using this measure for the first example:Measure = CALCULATE('Data Usage'[Total Usage],PREVIOUSMONTH('Data Usage'[Export Date].[Date]))&nbsp;That one didn't display any values in the matrix:&nbsp;&nbsp;

v-easonf-msft · Answer

Hi ,&nbsp;EuanKonnektis&nbsp;There's nothing wrong with&nbsp;Greg_Deckler&nbsp; solution.I suspect you didn't use the correct calendar table."Previousmonth" is a time intelligence function that needs to be used with the calendar table of consecutive date columns.&nbsp;Best Regards,Community Support Team _ EasonIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

euankonnektis · Answer

Hi&nbsp;v-easonf-msft&nbsp;- ahhh, fantastic, I see! My export date only has a date every week.&nbsp;So do I need to have a consecutive date calendar date table that has a relationship with the export date column?&nbsp;Many thanks,&nbsp;Euan.

euankonnektis · Answer

I do still seem to be having issues with this and "Difference" is currently returning a blank field.&nbsp;I have another table called 'App Usage' which has a date row that runs every day of the month (between 02-06-2019 to today).&nbsp;I have set up a relationship between 'Data Usage'[Export Date] and 'App Usage'[Date].&nbsp;Is there still something I'm missing? Apologies, I'm still getting used to this!&nbsp;Here is how it looks now:&nbsp;

Forum Discussion

Difference Measure between filtered column values in a matrix

5 Replies

Related content

Page Level filter for different groups

Dynamic measure filtering

Cumulative Difference from Another measure

Filter by Rank measure

Using measure as a filter