Identify missing dates in table

[Eyal]

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

[Eyal]

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

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

[Eyal]

@Ritaf1983 

thanks for reaching out, 
the source table :

Lot #	Date	Attribute	Value
Lot A	01/01/2024	Attribute 1	1
Lot A	02/01/2024	Attribute 1	3
Lot A	03/01/2024	Attribute 1	45
Lot A	04/01/2024	Attribute 1	5
Lot A	05/01/2024	Attribute 1	7
Lot A	06/01/2024	Attribute 1	2
Lot A	07/01/2024	Attribute 1	8
Lot A	08/01/2024	Attribute 1	9
Lot A	09/01/2024	Attribute 1	3
Lot A	10/01/2024	Attribute 1	2
Lot A	01/01/2024	Attribute 2	7
Lot A	02/01/2024	Attribute 2	5
Lot A	03/01/2024	Attribute 2	3
Lot A	04/01/2024	Attribute 2	7
Lot A	06/01/2024	Attribute 2	6
Lot A	07/01/2024	Attribute 2	3
Lot A	08/01/2024	Attribute 2	2
Lot A	10/01/2024	Attribute 2	2
Lot B	01/01/2024	Attribute 1	1
Lot B	02/01/2024	Attribute 1	3
Lot B	03/01/2024	Attribute 1	45
Lot B	04/01/2024	Attribute 1	5
Lot B	05/01/2024	Attribute 1	7
Lot B	06/01/2024	Attribute 1	2
Lot B	07/01/2024	Attribute 1	8
Lot B	08/01/2024	Attribute 1	9
Lot B	09/01/2024	Attribute 1	3
Lot B	10/01/2024	Attribute 1	2
Lot B	01/01/2024	Attribute 2	7
Lot B	03/01/2024	Attribute 2	3
Lot B	04/01/2024	Attribute 2	7
Lot B	05/01/2024	Attribute 2	8
Lot B	06/01/2024	Attribute 2	6
Lot B	07/01/2024	Attribute 2	3
Lot B	08/01/2024	Attribute 2	2
Lot B	09/01/2024	Attribute 2	6
Lot B	10/01/2024	Attribute 2	2

the resulting table should be the following :

Lot #	Date	Attribute
Lot A	05/01/2024	Attribute 2
Lot A	09/01/2024	Attribute 1
Lot B	02/01/2024	Attribute 2

thanks for your help

[Anonymous · Accepted solution]

@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

[Ritaf1983]

Hi @Eyal 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523