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