Retrieve the first date of the item's dissapearence

[Anonymous]

Hi,

I have the below table for which I would like to retrieve in a different column, the first date when the items were missing.  The missing date should be retrieved only in the next row of the missing date, for which I have added the comments.

Many thanks for your support,

Melissa

Item	Date	Comments
14544	02/10/2020
14544	02/13/2020	retrieve 02/11/2020 - the first date when the item was missing
14544	02/14/2020
14544	02/16/2020	do not retrieve 02/15/2020 - is not the first dissapearence of the item
14622	02/13/2020
14622	02/15/2020	retrieve 02/14/2020 - the first date when the item was missing
14622	02/17/2020	do not retrieve 02/16/2020 - is not the first dissapearence of the item
14622	02/19/2020	do not retrieve 02/18/2020 - is not the first dissapearence of the item

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

Sorry i don't quite understand what you meaning "retrieve the next day of the first occurrence".

You want each row show mindate+1?

Or you want show mindate+1 when an item is appearing only once?

If i misunderstand your meaning, please show me what's the expected out put you want.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @Anonymous 

I think you might encounter some issues if it not always the second day which is missing. If so then you can tweek @v-jayw-msft 's great solution like below:

Column = 
var mindate = CALCULATE(MIN('Fact'[Date]);ALLEXCEPT('fact';'fact'[Item]))
var maxdate = MAX( TODAY(); CALCULATE(MAX('Fact'[Date]);ALLEXCEPT('fact';'fact'[Item])))

return
MINX(
    FILTER(
        ALL('Calendar');
        'Calendar'[Date] in DATESBETWEEN( 'Calendar'[Date]; mindate; maxdate) && NOT('Calendar'[Date] IN CALCULATETABLE( SELECTCOLUMNS( 'Fact'; "Date"; 'Fact'[Date]); ALLEXCEPT( 'Fact'; 'Fact'[Item])))
    );
    'Calendar'[Date]
)

Kudos is highly appreciated.

[v-jayw-msft]

Hi @Anonymous ,

Please refer the following formula.

Column = 
var mindate = CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[Item]))
var datediff = DATEDIFF(mindate,'Table'[Date],DAY)
return
IF(datediff>1,mindate+1)

Result would be shown as below.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-jayw-msft 

The solution is working, but unfortunately is not capturing the scenario when an item is appearing only once. For this case, I will need to retrieve the next day of the first occurrence.

Many thanks,

Melisa

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

Sorry i don't quite understand what you meaning "retrieve the next day of the first occurrence".

You want each row show mindate+1?

Or you want show mindate+1 when an item is appearing only once?

If i misunderstand your meaning, please show me what's the expected out put you want.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-jayw-msft 

The second version is what I was looking for.

Many many thanks,

Melisa

[Anonymous]

Hi

I think the below code should do it for you:

First lost date = 
MINX(
    FILTER(
        ALL('Calendar');
        AND(
            'Calendar'[DateDateTime] IN DATESBETWEEN( 'Calendar'[DateDateTime]; MIN( 'Fact'[Date]); MAX( 'Fact'[Date]));
            NOT 'Calendar'[DateDateTime] IN VALUES( 'Fact'[Date])
        )
    );
    'Calendar'[DateDateTime]
)

I have a calendar table with all dates and my relationship is between the date in your fact table and the DateDateTime column.

If this helps then please mark it as the accepted solution. 

[Anonymous]

Hi @Anonymous 

I have tried to replicate your solution, but in the new column I don't get any values. Can you, please, share the PBIx file to check there the solution?

Thank you!

[Anonymous]

Hi @Anonymous 

I'm writing it as a measure. Do you need it as a calculated column?

[Anonymous]

Hi @Anonymous 

Yes, we need it in a new column.

Thank you!

[Anonymous]

Hi @Anonymous 

You can make it as a calculated table like the one below:

The methodology is:

• Group your fact table by item
• Calculate the [First lost date] using the measure from the first reply.