Forum Discussion

dpombal's avatar
dpombal
Icon for Post Partisan rankPost Partisan
8 years ago

DAX SCD Get amount between date from and date to

I am working with contracts data, this contract include a measure debt_amount. I should require to have the sum of amount of debt in a selected date.

 

 

My data( 5 contracts with contract_id from 1 to 5, with  date from, date to and version columns to create a SCD , and amount of debt as KPI)

 

 

 

Since there is an active flag, calculating current number of active contracts and current amount of debt is easy:

 

Current measures


amount_total = SUM(Contracts[amount_of_debt])
amount_active=CALCULATE([amount_total] , Contracts[active_flag]=1)

contracts_num_total = DISTINCTCOUNT(Contracts[contract_id])
contracts_num_active = CALCULATE([contracts_num_total] , Contracts[active_flag]=1)

 

 

My report ( I created a calendar table to check the debt available this days)

 

 

sdas

 

 

I need to create a measure which given 1 date, calculates the amount of date available this date.

 

SUM Debt = CALCULATE ( [amount_total]  ; Date_from <= Given_Date  AND Date_to <= Given_Date )

 

Date limits and versioning (

 If for a given contract there are 2 values  for a date limit ( get the one with max of version id )

Debt 07/08/2017  (7 august 2017)-->2000€

Debt 08/08/2017 (8 august 2017)- -->3000€ (version 3 of this contract)

 

 

 

 Here it is the PBIX file (https://1drv.ms/u/s!Am7buNMZi-gwkT0R9zM7HqrV2xmx)

 

 

I need something like

yyyy/mm/dd --> amount this day €

YEAR 2014 , 0 contracts active

2014/01/01 --> 0€

...

2014/02/01 --> 0€

...

YEAR 2015 , new contract_id =5 (1 active contract)

2015/01/01 --> 1000€

2015/02/01 --> 1000€

2015/03/01 --> 1000€

2015/03/15 --> 2000€

2015/04/01 --> 2000€

...

2015/12/31 --> 2000€

YEAR 2016 , new contract_id =3 ( 2 active contracts)

2016/01/01 --> 2300€

.... and so on

 

9 Replies