How to show a row is missing in a table based on date?

[EvaC]

Hi all,

There's a table I'm working on that has a date dimension set for the last 30 days. Data for this table is uploaded from a BI Warehouse and so changes everyday.

Other columns in the table are simply categories for company products and the data is revenue generated from each product for the last 30 days. I would like to introduce a feature on my app that lets us know if a date from the table is missing for the days specified. For example, the dates on the table could be; 25-08-2021, 26-08-2021, 28-08-2021 then this means the entire row of data for the dates 27th and 29th are missing for whatever reason.

Date	Product A	Product B	Product C
25-08-2021	xxx	xxx	xxx
26-08-2021	xxx	xxx	xxx
28-08-2021	xxx	xxx	xxx

Here is the problem. This can sometimes happen and we need to know which rows are missing so as to fix things in the backend. How can I show which rows are missing based on date?

[Or]

Load Date, ProductA, ProductB, ProductC

From YourTable;

//Get the last 30 days (including today, can be adjusted mathematically as required)

Temp:
Load date(Today()-30+rowno()) as MissingDateCheck
Autogenerate(30);

// Keep only the dates which are missing from the original table

Load MissingDateCheck as MissingDate Resident Temp
WHERE Not exists(Date,MissingDateCheck);

// Drop the Temp table which is no longer necessary

Drop Table Temp;

[Vegar]

Have you considered adding a master calendar to your data model.  If you create a master calendar containing the 30 days you would be able to identify missing dates in your data series by making a selection and examining the green, white and grey status of the date dimension field.