Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi community
I have a scenario where I'd like to calculate a % of reached duration.
Let's assume I have to Contracts A & B with a given contract duration ("Contract Duration").
The report user is now able to select a date from a slicer ("UserSelection Date").
Depending on this UserSelection Date I'd like to know what percentage of the "Contract Duration" was achieved.
The Total of both products is nearly 100%.
When the user changes the "UserSelection Date" to 14.09.2022 or later, the Total is 100%:
Now the calculation of the single rows is simple ("% of Duration reached Step 1"--> Date Diff UserSelection / Contract Duration)
But the calcuation of the Total differs, because you cannot take the sums of this two values.
That's why I'm calculating "% of Duration reached Step 2" with the AVERAGEX of the "% of Duration reached Step 1". This will return the correct value, but is extremly slow.
I have only around 2 Million rows and I'm using the import mode - but the calculation of the Step 2 takes around 1 Minute.
Additional information: "Product" is just one sample dimension, the formulas should work for other dimensions as well (i.e. vendor).
UserSelection Date = MAX(Calendar[Date])
Contract Duration =
CALCULATE(
SUMX(
myTable,
DATEDIFF ( myTable[Contract Begin], myTable[Contract End], DAY )
)
)
Date Diff UserSelection =
CALCULATE(
SUMX(
myTable,
DATEDIFF ( myTable[Contract Begin], [UserSelection Date], DAY )
)
)
% of Duration reached Step 1 =
IF(
MAX(myTable[Contract End]) < [UserSelection Date],
1,
IF(
MAX(myTable[Contract Begin]) > [UserSelection Date],
0,
[Date Diff UserSelection] / [Contract Duration]
)
)
% of Duration reached Step 2 =
AVERAGEX(
myTable,
[% of Duration reached Step 1]
)
Are there other ways to get the correct total value?
How can I improve the performance of this calculation?
Thank you very much for your support!
Hi,
I tried to recreate this with some test data that had 78k rows, so significantly less than your dataset.
Here's the server timings for your measures on this dataset:
I condensed it all into a single measure...
Avg Duration =
VAR _UserValue = MAX('User Selection'[Date])
RETURN
AVERAGEX(
myTable,
VAR _Num = MIN(_UserValue, myTable[Contract End]) - MIN(_UserValue, myTable[Contract Begin])
VAR _Denom = myTable[Contract End] - myTable[Contract Begin]
RETURN
DIVIDE(_Num, _Denom)
)
which gives these server timings. 16 storage engine queries down to 3 and a much faster time (for my test data anyway).
Hi @PaulOlding
Thank you very much for your support!
I've tried your formula and the performance is indeed way better.
Unfortunately I've made a mistake in the definition and the final value should be different.
Let me try to explain the expected results:
So I need two values which will be different on row level and on total level (G6 & E6) - I guess, there will be might be more than one calculation needed 🙂
Thank you and best regards
Tom
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |