Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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.
I can't download your file please share or make it public in OneDrive
Your solution is not valid for me, I need a measure which for a given date.
Say for example 1 January 2016 gets the sum of all contracts with this date between the range DATE_FROM and DATE_TO.
In my example for the 1st of January , sum will be 2300 . (2000 + 300). Imagine a Type 2 dimension with date from date to ranges, this is my use case.
something like this..but for my usecase
https://blog.gbrueckl.at/2012/02/handling-scd2-dimensions-and-facts-with-powerpivot/
Regards
Hi @dpombal,
Check this:
SUM Debt = CALCULATE ( [amount_total], FILTER ( Contracts, Contracts[date_from] <= MAX ( 'Date'[Date] ) && Contracts[date_to] >= MAX ( 'Date'[Date] ) ) )
https://1drv.ms/u/s!AlqSnZZUVHmshXIudzkJsDi7Hq9t
Thanks,
Xi Jin.
Hi @v-xjiin-msft,
Can we apply this solution using Aggregate tables ? if yes can you please explain how we can accomadate this on-the-fly measure calculation on Aggregate tables? I have tried using the aggregate tables, but the couldn't configure this DAX measure as Detail Column
Thanks,
Vamsi
Hi @v-xjiin-msft , your solution is not bad , however fails on days of time range change
Check for contract_id 5, 15 of March 2015 is suming (1000 + 2000 and creating a spike), while correct amount for this day is version 2 of this contract and sum of amount debt should be 2000 instead of 3000.
I managed to solve this with this version 2 formula changing >= by > in date_to:
SUM Debt v2 =
CALCULATE (
[amount_total];
FILTER (
Contracts;
Contracts[date_from] <= MAX ( 'Date'[Date] )
&& Contracts[date_to] > MAX ( 'Date'[Date] )
)
)
However instead of my solution , I will prefer something like given a date and a contract , get only the sum of debt amount of the contract line with MAX version.
I tried with no success with this formula
SUM Debt v3 =
CALCULATE (
[amount_total];
FILTER (
Contracts;
Contracts[date_from] <= MAX ( 'Date'[Date] )
&& Contracts[date_to] >= MAX ( 'Date'[Date] )
&& Contracts[version] = MAX(Contracts[version])
)
)
Here it is my updated PBIX https://1drv.ms/u/s!Am7buNMZi-gwkT0R9zM7HqrV2xmx
Hi @dpombal,
The MAX() function in "&& Contracts[version] = MAX(Contracts[version])" is returning the current row content not the MAX value. If you want to get the MAX version. You should do this:
MAX Version = CALCULATE ( MAX ( Contracts[version] ), FILTER ( Contracts, Contracts[date_from] <= MAX ( 'Date'[Date] ) && Contracts[date_to] > MAX ( 'Date'[Date] ) ) )
Then change the SUM Debt formula to:
SUM Debt v3 = CALCULATE ( [amount_total], FILTER ( Contracts, Contracts[date_from] <= MAX ( 'Date'[Date] ) && Contracts[date_to] >= MAX ( 'Date'[Date] ) && Contracts[version] = [MAX Version] ) )
So basically, it is same as the formula in SUM Debt v2. It also needs to change >= to >. Since SUM Debt v2 can return your expected result, why do you need this SUM Debt v3?
Modified pbix file: https://1drv.ms/u/s!AlqSnZZUVHmshXPt462d8-w6pTJK
Thanks,
Xi Jin.
q1:
Is MAX version measure operating at contract_id level,
Imagine contract_id 7 and contract_id 8 with version 2 and 3 changing the same day,
will this max measure operate for each contract_id and calculate max_version ?
question2 :
So basically, it is same as the formula in SUM Debt v2. It also needs to change >= to >. Since SUM Debt v2 can return your expected result, why do you need this SUM Debt v3?
reply 2:
In regards to this, I am trying to reproduce another usecases, with more than 1 change per day
Just one final question:
Imagine my final correct measure is SUM Debt v2
Using the Date Calendar table, currently SUM Debt v2 works using Date day on chart axis, what i should need for future is.
If viewing data at a month level:
Thanks in advance
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |