Comparison of amount between 2 periods

[Birinder]

Hi there,
I have two tables. On for period FA 21 and other for P9 21.
Actually They have every column similar except the amount column, and my main motive is to calculate the difference of P9 21 amount from FA 21. You can see the below screenshot to see the sample data and my desired result.

I've created a matrix with 2 rows and one column. Now I want top and bottom values difference from both periods.
Data was huge So i avoided doing merge.
I tried lookup but it is giving me, Multiple tables values supplied error.
Tried non blank methods. but the values weren't coming correct.
Tried the calculate sum and filter method but I am getting values wrong, except for the part when I choose the aggregation as minimum instead of sum, But even in that case, the difference is giving an error.
So for the conclusion, I am not able to get the results I desire.
It will be very nice of y'll, If you can help me in this,
I will be very thankful to you guys,
Peace and regards.

[amitchandak]

@Birinder , Based on what I got so far, You need something like this

How to use two Date/Period slicers

https://youtu.be/WSeZr_-MiTg

[Birinder]

@amitchandak 
I am sorry but it didn't help, I have 2 seperate data tables, In your video, It was only one.
So is there anything else.
Any different solution.
@BA_Pete @PaulDBrown 
Any help !

[amitchandak]

@Birinder , if you need two slicers to work, you need two tables. Both these table should join with one common date table , same case should work

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Birinder]

@amitchandak 
I am sorry, But there is no need of 2 slicers.
By 2 date period I meant there are 2 different tables.
One table which contains all the values for one period, and another table contains all the values for another period.
There is no specific column for period in both the cases.

Suppose in the SS above, Left hand side table is being created from data table which has all the data for period FA 21 and right hand side table is being created from data table which has all the data for period P9 21.
Now I only want difference of amount between these 2 tables, Every column name and count is same except the amounts for both tables.

[amitchandak]

@Birinder , if the two tables share common dimensions. Then across common dimension I can always take diff of measures

example

Sum(TableA[Value]) -Sum(TableB[Value])

You just need to display them across common dimesion.

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

[Birinder]

@amitchandak 
The solution is not working. I've tried it both on sample data and original data.
Its showing wrong differences.
Here is the link for the base data,
https://swissre-my.sharepoint.com/:x:/r/personal/birinder_singh_swissre_com/Documents/SAMPLE.xlsx?d=...

Here is the link for PBIX file:
https://swissre-my.sharepoint.com/:u:/r/personal/birinder_singh_swissre_com/Documents/SAMPLE.pbix?cs...

Request Access for this.
Insert the data in the form of SS above.

[Greg_Deckler]

@Birinder I cannot access the PBIX file at that link.

[Birinder]

@Greg_Deckler 
Hi greg, What exactly is the error.

[Greg_Deckler]

@Birinder Responding to your email. 2 ways to solve. First method:

1. Create a new table, Table = DISTINCT(SELECTCOLUMNS('FA 21',"Segment",[Segment],"Type",[Type]))
2. Create SegmentTypeKey columns in all three tables, SegmentTypeKey = [Segment] & "|" & [Type]
3. Relate the new table to the other two tables
4. Use the Segment and Type columns from the new table in your table/matrix
5. Use this measure, Bottom Measure = SUM('FA 21'[Bottom]) - SUM('P9 21'[Bottom]) and Top Measure = SUM('FA 21'[Top]) - SUM('P9 21'[Top])

Second method

1. Put your Segment and Type columns from FA 21 table into your matrix
2. Use measures like the following:

Bottom Measure =
  VAR __Segment = MAX('FA 21'[Segment])
  VAR __Type = MAX('FA 21'[Type])
  VAR __Bottom = SUM('FA 21'[Bottom]) //or just your measure for bottom
  VAR __P9Bottom = SUMX(FILTER('P9 21',[Segment] = __Segment && [Type] = __Type),[Bottom])
RETURN
  __Bottom - __P9Bottom

Top Measure =
  VAR __Segment = MAX('FA 21'[Segment])
  VAR __Type = MAX('FA 21'[Type])
  VAR __Top = SUM('FA 21'[Top]) //or just your measure for bottom
  VAR __P9Top = SUMX(FILTER('P9 21',[Segment] = __Segment && [Type] = __Type),[Top])
RETURN
  __Top - __P9Top

[Birinder]

Hi @Greg_Deckler ,
Thanks for the reply.
I just want to let you know that, values "Bottom" and "Top" are not directly sliceable.
They come under the column named "Layer".

Please refer the screenshot below,
The values are selected for Left side table in the uppermost SS.