Forum Discussion

bolabuga's avatar
bolabuga
Helper V
8 years ago

[ Retrieving the Last Date ]

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])))
Need Help

5 Replies

  • bolabuga's avatar
    bolabuga
    Helper V
    8 years ago

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

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    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