Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |