Dax function to calculate the missing dates instead of 0's and 1's

[sivarajan21]

Hi,

Need your help on this. I have these below 2 datasets:

Points Id	Data Date	Source
123	04/01/2022	Invoice
123	04/02/2022	Profile
123	04/07/2022	Invoice
123	04/08/2022	Profile
123	04/09/2022	Profile
123	04/10/2022	Profile
123	04/11/2022	Profile

Calendar date

Date

01/04/2022

02/04/2022

03/04/2022

04/04/2022

05/04/2022

06/04/2022

The above 2 tables are to be compared. What we need to find is the missing calendar dates of points id(data table). That is, the points id '123' in Data table has missing dates 03rd april 2022 that is present in calendar table. so we need to return that missing date in a table format/summarised table. 

Expected outcome

Points Id	Missing Calendar date
123	03/04/2022
123	04/04/2022
123	05/04/2022
123	06/04/2022

Now  @Ashish_Mathur  helped to create a measure to find the missing dates in terms of 0's and 1's. That helped me to create a calculated column to filter 0's & 1's to find the missing dates. Below is that measure. 
Test =
1 * ( [Calendar date value] <> [Data date value] )
Above same thing can be achieved from the below measure:

Exist_In_Table2 =
CONTAINS ( 'Calendar', 'Calendar'[Date], MAX ( Data[Date] ) ) * 1

Both these measures returns boolean/binary values(0's&1's)

But what I want to achieve from any of these measures is to return missing dates instead of 0's & 1's,. That is finding the missing dates from one measure instead of creating two. I am confused whether this can be achieved by aggregating function/iterating function.

Expected outcome

Points Id	Missing Calendar date
123	03/04/2022
123	04/04/2022
123	05/04/2022
123	06/04/2022

Remember outcome of the measure should be dates not 0's or 1's

Please let me know if you need further info

PFA file here

Can you all please help me with this?

Thanks in advance @grantsamborn @lbendlin  @Arul @amitchandak @Ashish_Mathur 

[sizi]

Hello all,

I need support in sorting this issue out. I have data where in it has multiple dates from assigned date to , planned date, actual date, approval date etc.

I created a date table and when i try to build a rleationship with any of the dates in the main data table, some of the records dont fetch as not all dates column have date in them.

I need a solution where in when i click date filter created from calendar table will filter the main data table to fetch the record without missing anything. If there isnt any date mentioned in the main data table, then insert date based on the primary key on the main table.

Below is the data for reference.  This is main data table

AssignedDate	Planned Start Date	Actual Start Date	Task No	Task Name	User
	11/21/2022 0:00	10/23/2023 21:00	101	Testing 1	A
	2/19/2023 0:00		102	Testing 2	B
	7/10/2023 0:00	7/10/2023 21:00	103	Testing 3	C
	9/11/2023 0:00	9/11/2023 21:00	104	Testing 4	D
	9/19/2023 0:00	9/19/2023 21:00	105	Testing 5	E
	10/31/2023 0:00		106	Testing 6	F
	11/1/2023 0:00	11/12/2023 21:00	107	Testing 7	G
	1/14/2024 0:00		108	Testing 8	H
	1/20/2024 0:00		109	Testing 9	I
1/31/2023 21:00	2/20/2023 0:00	2/20/2023 21:00	110	Testing 10	J
3/19/2023 21:00	8/7/2023 0:00	8/7/2023 21:00	111	Testing 11	K
5/28/2023 21:00			112	Testing 12	L
5/28/2023 21:00	6/17/2023 0:00	6/17/2023 21:00	113	Testing 13	M
5/28/2023 21:00	6/25/2023 0:00	6/25/2023 21:00	114	Testing 14	N
5/28/2023 21:00	8/28/2023 0:00	8/28/2023 21:00	115	Testing 15	O
6/7/2023 21:00	6/17/2023 0:00	9/17/2023 21:00	116	Testing 16	P
7/16/2023 21:00			117	Testing 17	Q
8/14/2023 21:00	8/23/2023 0:00	8/23/2023 21:00	118	Testing 18	R
8/21/2023 21:00	10/24/2023 0:00	10/10/2023 21:00	119	Testing 19	S
8/22/2023 21:00	8/28/2023 0:00	10/7/2023 21:00	120	Testing 20	T
8/29/2023 21:00	8/29/2023 0:00	8/29/2023 21:00	121	Testing 21	U
8/30/2023 21:00	7/2/2023 0:00	7/2/2023 21:00	122	Testing 22	V
8/30/2023 21:00	9/9/2023 0:00	9/9/2023 21:00	123	Testing 23	W
8/30/2023 21:00	10/2/2023 0:00	10/2/2023 21:00	124	Testing 24	X
9/2/2023 21:00	9/2/2023 0:00	9/2/2023 21:00	125	Testing 25	Y
9/2/2023 21:00	9/13/2023 0:00	9/13/2023 21:00	126	Testing 26	Z

This is calendar table :

Calendar Date

11/21/2022 0:00

2/19/2023 0:00

7/10/2023 0:00

9/11/2023 0:00

9/19/2023 0:00

10/31/2023 0:00

11/1/2023 0:00

1/14/2024 0:00

1/20/2024 0:00

2/20/2023 0:00

8/7/2023 0:00

6/17/2023 0:00

6/25/2023 0:00

8/28/2023 0:00

6/17/2023 0:00

8/23/2023 0:00

10/24/2023 0:00

8/28/2023 0:00

8/29/2023 0:00

7/2/2023 0:00

9/9/2023 0:00

10/2/2023 0:00

9/2/2023 0:00

9/13/2023 0:00

9/30/2023 0:00

9/6/2023 0:00

10/2/2023 0:00

9/9/2023 0:00

9/16/2023 0:00

9/12/2023 0:00

9/16/2023 0:00

9/16/2023 0:00

9/24/2023 0:00

10/21/2023 0:00

9/30/2023 0:00

10/17/2023 0:00

9/20/2023 0:00

9/23/2023 0:00

9/26/2023 0:00

9/25/2023 0:00

9/30/2023 0:00

10/1/2023 0:00

10/3/2023 0:00

6/20/2023 0:00

10/8/2023 0:00

10/14/2023 0:00

10/25/2023 0:00

10/16/2023 0:00

10/15/2023 0:00

10/24/2023 0:00

10/28/2023 0:00

9/24/2023 0:00

11/4/2023 0:00

11/6/2023 0:00

11/14/2023 0:00

11/13/2023 0:00

11/15/2023 0:00

11/25/2023 0:00

11/28/2023 0:00

I i connect date from calendar table to assigned date, then records where assigned date is blank goes missing and if i connect planned date then it date filters data from the date present in that column which again data is missed where assigned date is blank. I want date to filter all the records to be fetched  when date is toggled and shall show records from beginning till current date data. 

Kindly give in your input.

Thanks in advance. 

[Ahmedx]

If you have multiple IDs, then you can write the measure like this

[sivarajan21]

Hi @Ahmedx,

Wow! that's a brilliant solution

You made it look so easy. Your are correct, I have multiple points id and I need your this solution but it shows some error as below I have shown the screen shot.

Can you please have a look and help me to solve this error, if thats done i will accept this as solution and close this query?

Thanks in advance.

[Ahmedx · Accepted solution]

you must respect the order of the columns in the tables

[Clinical_Epi]

Hello - what do you return?

[Ahmedx]

[sivarajan21]

Hi @Ahmedx ,

Wow! this works amazingly well now. you made my day!

You not only gave me the solution but made me realize the importance of order of columns in measures. Going forward I will be careful in dealing with orders.

Didn't realise that order of columns would have this much effect on the result of dax.

Many thanks for your guidance and I will accept this as a solution.

Thank you and the community for everything

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26Rhy_CguGTOIzoMxyJ?e=JKSmuy