Forum Discussion

Eyal's avatar
Eyal
Helper II
2 years ago
Solved

Identify missing dates in table

I have a table witht he following columns : Lot #, Date, Attribute, Value i want to creat a table by dax that will lit the missing date for each Lpot # and Attribute. I tried using "ADDMISSINGITE...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Ritaf1983 Thanks for your contribution on this thread.

    Hi Eyal ,

    I created a sample pbix file(see the attachment), please check if that is what you want. You can follow the steps to get it:

    1. Create a date dimension table

    Date = VALUES('Table'[Date])

    2. Create a measure as below

    Flag = 
VAR _date =
    SELECTEDVALUE ( 'Date'[Date] )
VAR _lot =
    SELECTEDVALUE ( 'Table'[Lot #] )
VAR _attr =
    SELECTEDVALUE ( 'Table'[Attribute] )
VAR _tdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            'Table',
            'Table'[Lot #] = _lot
                && 'Table'[Attribute] = _attr
                && 'Table'[Date] = _date
        )
    )
RETURN
    IF ( _tdate = _date, 0, 1 )

    3. Create a table visual and apply a visual-level filter with the condition(Flag is 1)

    Best Regards

Eyal's avatar
Eyal
Helper II
2 years ago

Ritaf1983 

 

thanks for reaching out, 
the source table :

Lot #DateAttributeValue
Lot A01/01/2024Attribute 11
Lot A02/01/2024Attribute 13
Lot A03/01/2024Attribute 145
Lot A04/01/2024Attribute 15
Lot A05/01/2024Attribute 17
Lot A06/01/2024Attribute 12
Lot A07/01/2024Attribute 18
Lot A08/01/2024Attribute 19
Lot A09/01/2024Attribute 13
Lot A10/01/2024Attribute 12
Lot A01/01/2024Attribute 27
Lot A02/01/2024Attribute 25
Lot A03/01/2024Attribute 23
Lot A04/01/2024Attribute 27
Lot A06/01/2024Attribute 26
Lot A07/01/2024Attribute 23
Lot A08/01/2024Attribute 22
Lot A10/01/2024Attribute 22
Lot B01/01/2024Attribute 11
Lot B02/01/2024Attribute 13
Lot B03/01/2024Attribute 145
Lot B04/01/2024Attribute 15
Lot B05/01/2024Attribute 17
Lot B06/01/2024Attribute 12
Lot B07/01/2024Attribute 18
Lot B08/01/2024Attribute 19
Lot B09/01/2024Attribute 13
Lot B10/01/2024Attribute 12
Lot B01/01/2024Attribute 27
Lot B03/01/2024Attribute 23
Lot B04/01/2024Attribute 27
Lot B05/01/2024Attribute 28
Lot B06/01/2024Attribute 26
Lot B07/01/2024Attribute 23
Lot B08/01/2024Attribute 22
Lot B09/01/2024Attribute 26
Lot B10/01/2024Attribute 22


the resulting table should be the following :

Lot #DateAttribute
Lot A05/01/2024Attribute 2
Lot A09/01/2024Attribute 1
Lot B02/01/2024Attribute 2

 

thanks for your help

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Ritaf1983 Thanks for your contribution on this thread.

Hi Eyal ,

I created a sample pbix file(see the attachment), please check if that is what you want. You can follow the steps to get it:

1. Create a date dimension table

Date = VALUES('Table'[Date])

2. Create a measure as below

Flag = 
VAR _date =
    SELECTEDVALUE ( 'Date'[Date] )
VAR _lot =
    SELECTEDVALUE ( 'Table'[Lot #] )
VAR _attr =
    SELECTEDVALUE ( 'Table'[Attribute] )
VAR _tdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            'Table',
            'Table'[Lot #] = _lot
                && 'Table'[Attribute] = _attr
                && 'Table'[Date] = _date
        )
    )
RETURN
    IF ( _tdate = _date, 0, 1 )

3. Create a table visual and apply a visual-level filter with the condition(Flag is 1)

Best Regards