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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
09
Helper I
Helper I

The the last date an inventory discrepancy occurred for one material

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.

DateMaterialGap
11/02/2022RM0
14/02/2022RM1
15/02/2022RM1
16/02/2022RM1
17/02/2022RM1
18/02/2022RM1
21/02/2022RM1
22/02/2022RM1
23/02/2022RM0
24/02/2022RM0
25/02/2022RM0
28/02/2022RM0
01/03/2022RM0
02/03/2022RM0
03/03/2022RM0
04/03/2022RM0
05/03/2022RM0
06/03/2022RM0
07/03/2022RM0
08/03/2022RM0
09/03/2022RM0
10/03/2022RM0
11/03/2022RM0
13/03/2022RM0
14/03/2022RM0
15/03/2022RM0
16/03/2022RM0
17/03/2022RM0
18/03/2022RM0
19/03/2022RM1
20/03/2022RM1
21/03/2022RM1
22/03/2022RM1
23/03/2022RM1
1 ACCEPTED SOLUTION

Hi, @09 

If so, @johnt75 's solution should work for you.

I'm not sure what you mean by 'The above formula does not apply to this tool'.

At least, it works fine in my pivot table.

22.png

20.png

21.png

Please check my sample file for more information.

 

Best Regards,
Community Support Team _ Eason

 

View solution in original post

8 REPLIES 8
09
Helper I
Helper I

Hi@Johnt75,

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 )
        )
    )

18.png

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,

Hi, @09 

If so, @johnt75 's solution should work for you.

I'm not sure what you mean by 'The above formula does not apply to this tool'.

At least, it works fine in my pivot table.

22.png

20.png

21.png

Please check my sample file for more information.

 

Best Regards,
Community Support Team _ Eason

 

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  GAPDate
ABC     116/05/2022
ABC     115/05/2022
ABC    113/05/2022
ABC    112/05/2022

The result would be 12/05/2022

Hello all,

Any seggetion? 

Thank you!

johnt75
Super User
Super User

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 )
)

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.