Continous Date

[jrpoli2000]

Hi All!

I am not sure if this is possible with DAX.

I need to identify the invoice number per specific Client ID (if there is any) which is "continous" meaning the end date of the previous invoice number is the same with the start date of the succeding invoice.

Below is the desired out put (Status column).

Many thanks in advance.

INVOICE NO	CLIENT ID	START	END	STATUS
94153625	AA00-004-065	28/03/2018	24/04/2018	CONTINOUS
92085438	AA00-004-065	26/03/2018	28/03/2018
83617555	AA00-004-065	31/01/2018	04/02/2018
89428164	AA00-011-803	30/01/2018	05/02/2018
97112375	AA00-011-803	19/02/2018	22/02/2018
96455409	AA00-036-284	01/04/2018	30/04/2018
93790164	AA00-036-284	28/02/2018	31/03/2018	CONTINOUS
93433720	AA00-036-284	01/02/2018	28/02/2018
91616538	AA00-036-284	01/01/2018	31/01/2018
94355023	AA00-072-908	07/05/2018	09/05/2018
91784126	AA00-072-908	21/03/2018	22/03/2018

[OwenAuger · Accepted solution]

Hi @jrpoli2000

Here's one way of doing it with a DAX calculated column (called your table Invoices):

STATUS =
IF (
    NOT CALCULATE (
        ISEMPTY ( Invoices ),
        ALLEXCEPT ( Invoices, Invoices[CLIENT ID] ),
        TREATAS ( { Invoices[START] }, Invoices[END] )
    ),
    "CONTINUOUS"
)

The logic is to create a filter context with CALCULATE that clears all filters apart from the current row's CLIENT ID, and adds the current row's START as an END filter. Then check if the table is not empty in this context.

Regards,

Owen

[jrpoli2000]

Thanks a lot!!