Difference of sales for each date in Matrix

[Junaid11]

Hello,
I wanted to create a matrix from below data but it should show the difference for each day. I have attached the outcome as well.
The data is:

Sales number	Date	Revenue
A	01/01/2020	113
B	01/01/2020	225
C	01/01/2020	188
A	13/01/2020	146
B	13/01/2020	274
C	13/01/2020	199
A	18/01/2020	234
B	18/01/2020	345
C	18/01/2020	221

The matrix looks like this:

But I want the difference measure/column for each sales number on date sales. I have attached the outcome as well:

Sales Number	01/01/2020	Difference	13/01/2020	Difference	18/01/2020
A	113	33	146	88	234
B	225	49	274	71	345
C	188	11	199	22	221

Kindly let me know if possible.
Thanks

[parry2k · Accepted solution]

@Junaid11 here is the updated measure and the file is also attached

Prev Visible Day Revenue = 
VAR __transactionDate = MAX ( 'Date'[Date] ) 
VAR __prevVisibleDateRevenue = 
IF ( 
    NOT ISBLANK ( [Total Revenue] ), 
    VAR __prevVisibleDate = CALCULATE ( MAX ( 'Sheet1'[Date] ), 'Date'[Date] < __transactionDate ) 
    RETURN CALCULATE ( [Total Revenue], 'Date'[Date] = __prevVisibleDate ) )
  RETURN __prevVisibleDateRevenue

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@Junaid11 I think it is good, you don't have data for each day and the' previous day is getting calculated blank() and giving the wrong diff, we have to tweak it a bit.  Can you send me pbix file with the sample data?

[Junaid11]

Hello @parry2k ,

Yes the sales/REvenue data would be on different dates not on consecutive. I am attaching link og both the sample file of excel and Pbix. Thanks for you help as well.

https://drive.google.com/file/d/15JubO1Ai_-LIon6qwctZHG3VkeHHJbOX/view?usp=sharing

https://docs.google.com/spreadsheets/d/1ecSAn1F09lnY1RHFBHuDF4uG1pDfBc4F/edit?usp=sharing&ouid=11426...

[Junaid11]

Hello @parry2k,

I followed as you have asked but this is what I am getting. Kindly tell me is there any thing that I am doing wrong?

[parry2k]

@Junaid11 it is pretty straightforward, first, you add a date dimension in your model, you can follow my blog post to add one Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutio...

Once you date table and relationship with transaction table, add the following measures:

Sales = SUM ( Table[Sales] )

Prev Day Sales = CALCULATE ( [Sales], PREVIOUSDAY ( DateTable[Date] )  )

Diff = [Sales] - [Prev Day Sales]

To visualize, use matrix visual, put sales number on rows, date from date table on columns and above measure (sales and Diff) on values and you have the solution

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡