Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hello PBI Community,
I'm stuck on trying to figure out how to make a dax calculation for the following data. I need a calculation that will count sequentially by ID the days between two dates using a certain logic. Ideally need this calculation to be sliced by year, month, day, e
Here is the data and my current excel formula. The formula looks at if the seq is 10 than the calculation is between the complete date and the start date if the seq is greater than 10 than the calculation looks at the date difference between the complete date and the complete date previous to that.
For example: The first ID show would be 1 in the first cell and 12 in the second. This is per ID by sequence number. Any help is greatly appreciated. Thank you!
ID | Item ID | StartDate | SEQ | Quantity | Complete Date |
5275554 | S25PG100DZXX | 5/6/19 | 10 | 15 | 5/7/19 |
5275554 | S25PG100DZXX | 5/6/19 | 20 | 15 | 5/19/19 |
5275554 | S25PG100DZXX | 5/6/19 | 30 | 15 | 8/29/20 |
5275555 | S25PG100DZXX | 5/6/19 | 10 | 16 | 5/7/19 |
5275555 | S25PG100DZXX | 5/6/19 | 20 | 16 | 5/20/19 |
5275555 | S25PG100DZXX | 5/6/19 | 30 | 16 | 8/29/20 |
5275556 | S25PG100DZXX | 5/6/19 | 10 | 16 | 5/7/19 |
5275556 | S25PG100DZXX | 5/6/19 | 20 | 16 | 5/20/19 |
5275556 | S25PG100DZXX | 5/6/19 | 30 | 16 | 8/29/20 |
5278216 | S25PG100DZXX | 6/4/19 | 10 | 20 | 6/6/19 |
5278216 | S25PG100DZXX | 6/4/19 | 20 | 20 | 6/6/19 |
5278216 | S25PG100DZXX | 6/4/19 | 30 | 20 | 6/6/19 |
5278216 | S25PG100DZXX | 6/4/19 | 40 | 20 | 6/23/19 |
5278216 | S25PG100DZXX | 6/4/19 | 50 | 20 | 6/25/19 |
5278216 | S25PG100DZXX | 6/4/19 | 60 | 20 | 6/28/19 |
5278826 | S25PG100DZXX | 6/10/19 | 10 | 16 | 6/12/19 |
5278826 | S25PG100DZXX | 6/10/19 | 20 | 16 | 6/18/19 |
5278826 | S25PG100DZXX | 6/10/19 | 30 | 16 | 9/21/20 |
5278831 | S25PG100DZXX | 6/10/19 | 10 | 16 | 6/17/19 |
5278831 | S25PG100DZXX | 6/10/19 | 20 | 16 | 6/18/19 |
5278831 | S25PG100DZXX | 6/10/19 | 30 | 16 | 9/21/20 |
5280443 | S25PG100DZXX | 6/26/19 | 10 | 4 | 7/3/19 |
5280443 | S25PG100DZXX | 6/26/19 | 20 | 4 |
Solved! Go to Solution.
Hi, @MSW ,
Try it.
Measure = var _a=CALCULATE(MAX('Table'[Complete Date]),FILTER(ALL('Table'),[ID]=MAX([ID])&& [EQ]<MAX('Table'[EQ])))
return
IF(MAX('Table'[EQ])=10,DATEDIFF(MAX('Table'[StartDate]),MAX('Table'[Complete Date]),DAY),DATEDIFF(_a,MAX('Table'[Complete Date]),DAY))
The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @MSW ,
Try it.
Measure = var _a=CALCULATE(MAX('Table'[Complete Date]),FILTER(ALL('Table'),[ID]=MAX([ID])&& [EQ]<MAX('Table'[EQ])))
return
IF(MAX('Table'[EQ])=10,DATEDIFF(MAX('Table'[StartDate]),MAX('Table'[Complete Date]),DAY),DATEDIFF(_a,MAX('Table'[Complete Date]),DAY))
The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @MSW
Sorry but I have some problems to follow what it is you want. Could you please be more specific? Please refer to this article for ideas about how to make your question a good one: How to Get Your Question Answered Quickly (powerbi.com)
It's always good to place in here the starting point, the reasoning for achieving the final result and the final result itself. This will immensely increase the chances of addressing your problem.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
9 |
User | Count |
---|---|
28 | |
23 | |
12 | |
11 | |
10 |