Calculate difference between Matrix Columns

[EuanKonnektis]

Hi Everyone,

I feel like I've seen a multitude of solutions for questions similar to my own here, but can't find the one that tackles the way my report is built! If anyone is able to help me, it would be greatly appreciated as I've been trying to tackle this for some time!!

I export a data set into an excel spreadsheet every now and again. The report is named the date it is exported and it is filled with ID numbers which each have a value at the time of export. All the spreadsheets are saved into a folder and Power BI uses that folder to display data. The name of the excel sheet is transformed into a date field so I can see the value of an ID number on the date the data set was exported.

I have got the values running in a matrix with the export date as the column, the ID as the row:

I have another table that does have data running on a daily basis - I have Export Date set up with a relationship to the Date column of the other table.

What I need is a way of showing the difference between the two dates in this table. So, I want (06-05-2020 - 04-05-2020). 

Is there any way that I can create a column that would be able to sit in between each matrix column and work out the difference? The report just continues all the time, so I'd always need it to look at date right and subtract date left value.

Many thanks in advance!

[dax]

Hi  @EuanKonnektis , 

You coudld try to create an index column based on date group by name like below

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIyMDLQN9U3ATINlWJ1UATNQEx0QUNDINsYLJqEpt8IXRRkgKEBuijYBCOg4lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, date = _t, amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"date", type date}, {"amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"name"}, {{"all", each _, type table [name=text, date=date, amount=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([all],"index", 1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"all"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"date", "amount", "index"}, {"date", "amount", "index"})
in
    #"Expanded Custom"

 Then use measure like below

Measure 4 =
CALCULATE ( SUM ( t2[amount] ) )
    - CALCULATE (
        SUM ( t2[amount] ),
        FILTER ( ALLEXCEPT ( t2, t2[name] ), t2[index] = MIN ( t2[index] ) - 1 )
    )

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@EuanKonnektis 

You can create a last day measure and take diff and use that

Last Day Non Continous = CALCULATE(sum('order'[Qty]),filter(all(Table),Table[Date] =MAXX(FILTER(all(Table),Table[Date]<max(Table[Date])),Table[Date])))

or
Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd(Table[Date],-1,Day))

This Day = sum('order'[Qty])

diff = [This Day ] -[Last Day Non Continous] // Or use [Day behind Sales]

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[EuanKonnektis]

Hi @amitchandak, thanks ever so much for your advice here! I've used the day behind sales to help with tracking the increase over -7 days which has been really useful!

I just haven't cracked using the Non-Continous Formula you've provided to help with when the export dates don't have a continuous 1,2,3 days export. I wondered if you could take a look at what I've tried? I'm not sure I've written your measure correctly. So this is the measure I'm trying to build to use to compare with the latest Export figure.

Here's a key to help show what I'm working with.

Table = 'Data Usage'

Value = 'Data Usage[Usage (MB)]

Date value = 'Data Usage'[Export Date]

Previous Export = CALCULATE(SUM('Data Usage'[Usage (MB)]),FILTER('Data Usage','Data Usage'[Export Date] =MAXX(FILTER('Data Usage','Data Usage'[Export Date]<MAX('Data Usage'[Export Date])),'Data Usage'[Export Date])))

Thanks in advance,

Euan.