Forum Discussion

jrpoli2000's avatar
jrpoli2000
Frequent Visitor
8 years ago
Solved

Continous Date

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 NOCLIENT IDSTARTENDSTATUS
94153625AA00-004-06528/03/201824/04/2018CONTINOUS
92085438AA00-004-06526/03/201828/03/2018 
83617555AA00-004-06531/01/201804/02/2018 
89428164AA00-011-80330/01/201805/02/2018 
97112375AA00-011-80319/02/201822/02/2018 
96455409AA00-036-28401/04/201830/04/2018 
93790164AA00-036-28428/02/201831/03/2018CONTINOUS
93433720AA00-036-28401/02/201828/02/2018 
91616538AA00-036-28401/01/201831/01/2018 
94355023AA00-072-90807/05/201809/05/2018 
91784126AA00-072-90821/03/201822/03/2018 
Need Help
  • OwenAuger's avatar
    OwenAuger
    8 years ago

    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

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    8 years ago

    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