How can I create a table comparing Values from 2 Selected Dates?

[PBIUWO]

Hi, 

I have a datasource that is a folder. It contains an accumilation of Excel report that is run monthly with the columns, Item Code, Inventory Quantity, and Location. 

Within Power BI, these are imported with the column "File Date" to keep record of the reports in different time periods.

The Power BI that I want to create has an end goal of comparing Inventory Quantity between 2 time periods. 

The users will select the 2 File Date (time period as a Dropdown Slicer), and the Matrix Table showing Inventory Quantity by Item Code will be filtered. 

The Matrix Table will show, Item Code, Inventory Quantity (Date 1), Inventory Quantity (Date 2), Difference in Quantity Between the 2 Periods. 

Something like this,

Item Code	Earlier File Date Quantity	Latter File Date Quantity	Quantity Difference
ABC	3	1	2

3 Problems that I am facing:

1. I can solve for this easily by having the "File Date" as the Matrix Column, and "Quantity" as the Matrix Value. But I cannot do the Quantity Difference calculation without it duplicating it, so I prefer it to be a Measure. 

2. To solve for problem 1, I am using the Calculate() and Filter(), for example, I am trying to use =CALCULATE(SUM(Item Code)), FILTER(MIN(FILE DATE) to show the Earlier File Quantity , but it doesn't seem to work. It just shows the total quantity between the Earlier and Latter File Dates. 

Using Variables, I was able to solve for it. (Please let me know if this is the right approach) 

Earlier Date Quantity =

VAR MINDATE = MIN([File Date])

VAR MINDATEFROMSELECTION = CALCULATE(MIN([File Date]), [File Date] >= MINDATE)

RETURN

CALCULATE(SUM(Inventory Quantity), FILTER([File Date].[Date] = MINDATEFROMSELECTION))

BUT Problem 3 occured.

3. There can be Item Codes that are removed or is newly added, so it can be missing from either files. In this case, it would still need to calculate it and show that the Inventory Quantity has increased or decreased. If an item was there in the Earlier File, and did not appear in the Latter File and lets say it had Quantity of 3, I would need for it to say -3. 
(Ex. If the item didn't appear in the Latter File, the quantity would just be the same as the Earlier File Quantity because it thinks that it is the MIN/MAX in the File Date Filter in the Calculation )

Hope I explained it well.

Thank you! 

[amitchandak]

@PBIUWO , Based on the initial description, I think this blog of mine can be a bit of help, check if this can help

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

Or Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[PBIUWO]

@amitchandak  

Thank you, 
I just read your blog post, and I think have solved it for that part. 

I am comparing the same item, and it's quantity in different time periods. Where as you are comparing the sales between 2 time periods. 
It's the problem 3 that I have highlighted that is the issue right now because I have to show the difference of quantity between the 2 time period, but sometimes the Item is there for one period and not there for the other.

I have created a sample data below to replicate the problem:

*** I am unable to post a HTML table due to errors with this website. 

File Date |  Item Code | Item Quantity
08/31/2019 | ABC-123 | 3
08/31/2019 | AAA-123 | 4
08/31/2019 | BBB-123 | 1
08/31/2019  | CCC-123 | 3
01/31/2021 | ABC-123 | 3
01/31/2021 | AAA-123 | 3
01/31/2021 |  BBB-123 | 2
01/31/2021 | CCC-123 | 3
01/31/2021 | DDD-123 | 5

Result that I want as a Matrix: 

Item Code | Earlier File Date Quantity | Latter File Date Quantity | Quantity Difference
ABC-123 | 3 | 3 | 0
AAA-123 | 4 | 3 | -1
BBB-123 | 1 | 2 | 1
CCC-123 | 3 | 3 | 0
DDD-123 0 | 5 | 5

Thank you 

[amitchandak · Accepted solution]

@PBIUWO , check page 14 on the file attached after signature 

[PBIUWO]

@amitchandak  

Was able to reference it and it works.

THank you!