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 "ADDMISSINGITEMS", but could not find the correct context .
any thoughts?

 

Eyal

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

5 Replies

  • 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

  • Eyal's avatar
    Eyal
    Helper II
    2 years ago

    @Ritaf1983 this works wonderfuly 

    many thanks,
    Is there a way to sum the nuber of rows with flag=1? I couldnt figure it out

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Eyal ,

      You can create a measure as below to get it:

      Count of Missing Dates = 
VAR _tab=EXCEPT(
    CROSSJOIN(
        VALUES('Table'[Lot #]),
        VALUES('Table'[Attribute]),
        VALUES('Date'[Date])
    ),
    SELECTCOLUMNS(
        'Table',
        "Lot #", 'Table'[Lot #],
        "Attribute", 'Table'[Attribute],
        "Date", 'Table'[Date]
    )
)
RETURN COUNTROWS(_tab)

      Best Regards