Forum Discussion

Eyal's avatar
Eyal
Icon for Helper II rankHelper 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