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 invoi...
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

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

jrpoli2000's avatar
jrpoli2000
Frequent Visitor
8 years ago

Thanks a lot!!