Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I have a table which requires me to group by time to maturity. This is done directly in Power Query where the "TimeToMaturity" is calculated by subtracting "As Of Date" with "MaturityDate". After this I have grouped them by adding a column named "MaturityBucketName" and "MaturityBucketID" with an if statement based on the column "TimeToMaturity" in the following categories:
MaturityBucketName | MaturityBucketID |
Under 2.25 years to maturity | 1 |
Between 2.25 and 3.25 years to maturity | 2 |
Above 3.25 years to maturity | 3 |
In my report all contracts is then grouped by these in a matrix, which consists of the following rows:
Then on the same page I have some other timeseries data, which is calculated using a between slicer, which also affects my ContractIDs as when they change groups in my grouping data, they are getting duplicated, so the same contract is now both in i.e. Between 2.25 and 3.25 years to maturity and under 2.25 years to maturity.
Does anyone know how to solve this issue, I have tried generating this in DAX with a max function on date, but it does not allow me to apply it on the row level?
Best Regards.
Solved! Go to Solution.
Hello @Solle,
Can you please try this:
In Power Query, instead of directly calculating "TimeToMaturity" as a column, consider creating a calculated column that determines the "MaturityBucketID" based on the original "MaturityDate" and "As Of Date" columns.
This calculated column should be assigned based on the criteria you specified (Under 2.25, Between 2.25 and 3.25, Above 3.25), using IF statements or custom functions.
MaxMaturityBucketID =
CALCULATE(
MAX('YourTable'[MaturityBucketID]),
ALLSELECTED('YourTable'),
'YourTable'[As Of Date] >= MIN('DateSlicer'[StartDate]) &&
'YourTable'[As Of Date] <= MAX('DateSlicer'[EndDate])
)
Should you require further details or information, please do not hesitate to reach out to me.
Hello @Solle,
Can you please try this:
In Power Query, instead of directly calculating "TimeToMaturity" as a column, consider creating a calculated column that determines the "MaturityBucketID" based on the original "MaturityDate" and "As Of Date" columns.
This calculated column should be assigned based on the criteria you specified (Under 2.25, Between 2.25 and 3.25, Above 3.25), using IF statements or custom functions.
MaxMaturityBucketID =
CALCULATE(
MAX('YourTable'[MaturityBucketID]),
ALLSELECTED('YourTable'),
'YourTable'[As Of Date] >= MIN('DateSlicer'[StartDate]) &&
'YourTable'[As Of Date] <= MAX('DateSlicer'[EndDate])
)
Should you require further details or information, please do not hesitate to reach out to me.
Hi @Sahir_Maharaj,
Sorry for the late reply.
I just tested your solution and it works smoothly, thank you for helping and taking your time to reply to my issue.
Best Regards.
User | Count |
---|---|
57 | |
22 | |
21 | |
19 | |
16 |
User | Count |
---|---|
87 | |
85 | |
52 | |
37 | |
23 |