Forum Discussion
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
- v-xjiin-msft
Solution Sage
Hi dpombal,
Did you mean something like this?
https://1drv.ms/u/s!AlqSnZZUVHmshW2Fa2iQGGF5053j
If not, please make a more detailed explaination about your requirement.
Thanks,
Xi Jin.- dpombal
Post Partisan
I can't download your file please share or make it public in OneDrive
- v-xjiin-msft
Solution Sage