Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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
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
Thanks a lot!!
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |