Grouped by time to maturity with between slicer

[Solle]

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:

• MaturityBucketName
• ContractID

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.

[Sahir_Maharaj · Accepted solution]

Hello @Solle,

Can you please try this:

1. 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.

2. 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.

3. Create a measure that calculates the maximum "MaturityBucketID" for each ContractID based on the filtered time range from the slicer. When you're creating your matrix with "MaturityBucketName" and "ContractID" as rows, use the "MaxMaturityBucketID" measure as the values.

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.

[Solle]

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.