Compute delta of values in the same column depending on two category columns

[Anonymous]

Hi all,

I have a table with the following 3 relevant columns:

• Start date
• Maturity Date
• Amount value

My goal is to create the following visual, where for each time period (month, year) I show the Start date amount minus Maturity date Amount (e.g. for 10 January I will have let's say 2 rows with Start date = 10 January and 4 other rows with Maturity date = 10 January. I want the delta of the amount of these 6 rows such as (sum of the 2 Start date rows amount) - (sum of the 4 Maturity date rows amount).

For now, I solved by duplicating the fact table and connecting both of them to the calendario table with the different dates (one is connected with Starts, other with Maturity). See below  

The issue is that if I want to add new data, I have to add such data to both the original fact table and the duplicate table

Does anyone have a solution to have the same result but without duplicating the fact table?

Thanks

Stefano

[amitchandak]

@Anonymous , You need to create a common date table. Join it with both dates. One join will be inactive that you need to activate using userelationship 

Assume start date is active, then ,measure is

sum(Table[Amount value])

and  Maturity Date is inactive

calculate(sum(Table[Amount value]) , USERELATIONSHIP ('Date'[date], Table[Maturity Date]))

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi