How to get the FIRST Date in a date column

[Sowmiya_C]

Hi All,

Here is a requirement to get the FIRST date and Last Date of the date column against the sales order. Please find the below screenshot.

Against one sales order ID, there is multiple start service date and end service date. In that, the requirement is to fetch the FIRST date of the start service date as highlighted in the screenshot.

The FIRST and LAST dates need to be maintained in a separate column.

Let me know any workaround.

Thanks,
sowmiya

[horynao]

Hello,

I try to figure out this task, and I suppose it could be done with one aggregation measure and one measure based dimension per start and end date.

First of all, there needs to be add start and end date of service to model as measures. It could by done in model level too, but I supply these in source file.

Then you create at Story level aggregation measure with function FIRST or LAST with measure SSD_MEAS or ESD_MEAS (Start service date and so..) with context of SALES_ORDER and STAR_SERVICE_DATE (or END_SERVICE_DATE for end date).

Then you create measure-based dimension with generated members and context Category and SALES_ORDER.

I hope this can help even a little, I didnt do some tests for exceptions and so..

Best regards

Ondrej

[Sowmiya_C]

Thanks for your response!

I tried this but ,I need only FIRST date as output.In this it is showing all the dates.

[horynao]

Hello,

would you please provide some MS Excel example? It could illustrate the task better I think.

Thank you in advance

Best regards

Ondrej

[sheel-haria]

Did you get a solution for this? I am facing the same issue? Can we use and Advanced formulas to tag the first and last dates?