topic Re: Grouped by time to maturity with between slicer in DAX Commands and Tips

Grouped by time to maturity with between slicer

Solle — Wed, 30 Aug 2023 06:28:12 GMT

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.

Re: Grouped by time to maturity with between slicer

Sahir_Maharaj — Sat, 02 Sep 2023 20:14:23 GMT

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

Re: Grouped by time to maturity with between slicer

Solle — Thu, 07 Sep 2023 06:01:31 GMT

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.