Alternative of group by in Pigment

Hi Ouma,

I’ve tried to make a quick repro of your model.

The easiest way I’ve found was to add new properties in my Customer dimension, since you want to know the max end date per customer and the lastest duration attached per customer.

Then, if needed it could be applied it on all lines of the MRR-D Invoice dimension.

First, here is my source data : MRR-D Invoice dimension

In green I highlighted the values we want to return per customer on the Latest Subscription duration column.

I made a few other examples for when a customer has only one subscription, or two on the same day.

Then I opened my Customer dimension and created two properties:

Last subcription end date

formula is

'MRR-D Invoice'.End_date [by max: 'MRR-D Invoice'.Customer_reference]

I looking for the maximum value of end dates per Customer

and

Latest subcription duration

if(('MRR-D Invoice'.End_date = Customer.'last subscription end date'), 'MRR-D Invoice'.Subscription_duration)[by lastnonblank: 'MRR-D Invoice'.Customer_reference]

I compare the end date in the MRR-D invoice dimension with the max end date from my Customer Dimension, if it matches, I return the duration per customer with a lastnonblank sort, meaning, if I have two results (like the Black customer here in my example), I will return the last result, but I can change to firstnonblank if needed.

Now that I have my values (Last date + Last duration) per Customer, I can return them on my MRR-D Invoice dimension if needed.

Latest_Subcription_duration

'MRR-D Invoice'.Customer_reference.'Latest_subscription duration'

I am doing a chaining to return the property Latest subscription duration from the Customer dimension using the Customer property of the MRR-D Invoice dimension.

Hope this helps.

Best,