Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi, I'm having trouble with generating the first date of discrepancy stock between two softwares. That's mean, I want to write a DAX formula that returns the date of which the gap first happens.
The date that i want is 19/03 for this example below taken in account that a gap happened before on 14/02and it was regulated. Thanks for your help.
Date | Material | Gap |
11/02/2022 | RM | 0 |
14/02/2022 | RM | 1 |
15/02/2022 | RM | 1 |
16/02/2022 | RM | 1 |
17/02/2022 | RM | 1 |
18/02/2022 | RM | 1 |
21/02/2022 | RM | 1 |
22/02/2022 | RM | 1 |
23/02/2022 | RM | 0 |
24/02/2022 | RM | 0 |
25/02/2022 | RM | 0 |
28/02/2022 | RM | 0 |
01/03/2022 | RM | 0 |
02/03/2022 | RM | 0 |
03/03/2022 | RM | 0 |
04/03/2022 | RM | 0 |
05/03/2022 | RM | 0 |
06/03/2022 | RM | 0 |
07/03/2022 | RM | 0 |
08/03/2022 | RM | 0 |
09/03/2022 | RM | 0 |
10/03/2022 | RM | 0 |
11/03/2022 | RM | 0 |
13/03/2022 | RM | 0 |
14/03/2022 | RM | 0 |
15/03/2022 | RM | 0 |
16/03/2022 | RM | 0 |
17/03/2022 | RM | 0 |
18/03/2022 | RM | 0 |
19/03/2022 | RM | 1 |
20/03/2022 | RM | 1 |
21/03/2022 | RM | 1 |
22/03/2022 | RM | 1 |
23/03/2022 | RM | 1 |
Solved! Go to Solution.
Thank you for replying. I have to work with powerpivot in Excel.
The above formula will not work in this tool.
Hi, @09
If you want to add a calculated column in powerpivot, try to change the measure formula as below:
Result=
VAR currentValue =
CALCULATE ( LASTNONBLANK ( Table1[Gap], 1 ), ALL ( 'Table1' ) )
VAR lastNonGap =
CALCULATE (
MAX ( 'Table1'[Date] ),
FILTER ( ALL ( 'Table1' ), 'Table1'[Gap] = 0 )
)
RETURN
IF (
currentValue = 1,
CALCULATE (
MIN ( 'Table1'[Date] ),
FILTER ( ALL ( 'Table1' ), 'Table1'[Gap] = 1 && 'Table1'[Date] > lastNonGap )
)
)
Best Regards,
Community Support Team _ Eason
Thanks for your help.
I would like to know if it is possible to work with measures because the table in the example is actually a pivot table.
Regards,
Thank you all for your efforts @v-easonf-msft and @johnt75 .
The formula finally works for me.
Best regards,
Hello all,
The solution that you gave to me works fine. So grateful for that! but i have a problem with a particular case when i always have a discrepancy of stock (which means the earliest date is actually the first date a discrepancy happen). I'll give you an exemple of this case below. Thanks a lot for any help!
Material | GAP | Date |
ABC | 1 | 16/05/2022 |
ABC | 1 | 15/05/2022 |
ABC | 1 | 13/05/2022 |
ABC | 1 | 12/05/2022 |
The result would be 12/05/2022
Hello all,
Any seggetion?
Thank you!
You can create a measure like
Discrepancy date =
var currentValue = SELECTCOLUMNS( TOPN(1, 'Table', 'Table'[Date], DESC), "@val", 'Table'[Gap])
return IF( currentValue = 1,
var lastNonGap = CALCULATE( MAX('Table'[Date]), 'Table'[Gap] = 0)
return CALCULATE( MIN('Table'[Date]), 'Table'[Gap] = 1, 'Table'[Date] > lastNonGap )
)
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
15 | |
14 | |
13 | |
9 |
User | Count |
---|---|
60 | |
23 | |
22 | |
19 | |
13 |