[ Retrieving the Last Date ]

[bolabuga]

Hello everyone,

I have the following scenario:

Revenue and Service tables don’t have a relationship (i don’t know how to handle it, it would be a many to many relationship based on client column)

Im creating a calculated column on revenue table, where I need to iterate the rows searching for the amount of services the client had over a period of time between -405 days and -40 days (no problem in here).

If the amount computed is >= 1, i need to retrieve the Last service date (by last i mean that: if i found 2 services date in the given period, date: 11/2/2017 and date: 25/7/2017, i want to retrieve 25/7/2017).

One problems is that "LASTDATE" can’t be used, as the service table has cases with same services in the same date for the same client, which I would be interested in any of the last repeated dates.

Using SUMX i can make it work right for cases where there’s only 1 service in the period consisted.

CALCULATED COLUMN LAST SERVICE  DATE = 
IF(
    COUNTROWS(
        FILTER(
            SERVICES;
            SERVICES[CLIENT] = REVENUE[CLIENT]
            && SERVICES[GROUP] = 1
            && ( SERVICES[DATE] >= REVENUE[DATE]) - 405 && SERVICES[DATE] <= REVENUE[DATE]) - 40 ))) >= 1;
SUMX(
        FILTER(
            SERVICES;
            SERVICES[CLIENT] = REVENUE[CLIENT]
            && SERVICES[GROUP] = 1
            && ( SERVICES[DATE] >= REVENUE[DATE]) - 405 && SERVICES[DATE] <= REVENUE[DATE]) - 40 ));
SERVICE[DATE])))

[v-yulgu-msft]

Hi @bolabuga,

Could you please provide detailed sample data? It would be helpful for us to better understand your scenario and test for you.

Regards,
Yuliana Gu

[bolabuga]

@v-yulgu-msft

Hi v-yulgu,

I did a test database and replicated the scenario that some insights would be great!!!

https://drive.google.com/open?id=0B8Aq8DhGApJqWDZlLTdQYnFibDQ

[Zubair_Muhammad]

Hi @bolabuga

Try replacing SUMX with MAXX

[bolabuga]

@v-yulgu-msft

I will try to mount a test base to replicate this.

[bolabuga]

It seens i manage to do the last part of the calculated column right, after reading some of the posts in the forum.

If any one can comment on this possible solution, i would be greatful, because there could be a better way to do this and im not seeing it. 

CALCULATE(
          MAX(SERVICES[DATE]);
          FILTER(
               SERVICES;
               SERVICES[CLIENT] = REVENUE[CLIENT]
               && SERVICES[GROUP] = 1
               && ( SERVICES[DATE] >= REVENUE[DATE] - 405 && SERVICES[DATE] <= REVENUE[DATE] - 40 )))))