Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jrpoli2000
Frequent Visitor

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 
1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Thanks a lot!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.