find the dates in a column that is not present in another column

[sivarajan21]

Hi,

I have 2 questions.

1) I have four data(fact) tables that needs to be combined(union-summarize) together into one table using Dax. I know this can be done in power query but i want to improve the performance of the report. so i wanted to be done using Dax. below is the four tables screen shot

Now unioning this into one table, I need to map it as below which is shown here. I can do this,but my real question begins here.

2) i) I need to find the fact table(table resulting from dax union) Date column(Dates) that is missing in Calendar table(Date) column using dax.
ii)Find the count of above missing dates using dax.
It is fine if the above two dax are different measures or a same measure. The points id(fact table) can be used here as a filter criteria also if you need.

FYR, i have attached the file in below link 

Financial mang.pbix

can you please guide me on this?

Thanks in advance

[sivarajan21]

Hi,

Many thanks for showing this visual that aggregates units and this works brilliantly. when we remove last update column and wow it works amazingly. Now, with regards to the 'Missing' measure, currently it uses only the date columns from both tables(Data, calendar). I think that is one reason it is giving no missing dates. But what we want from this measure to show,

for any given points level(Points column),within data table,we need to find out how many data table dates(col) are missing from the calendar table date.if we aggregate to a points level, then surely we should have a missing dates. for example, as shown in below for illustration to give a clear info,

Then we need to create a visual(expected output) with the points id,missing dates and their missing count on a visual.For example below table achieved from a measure

If this is achieved, then i can close this query.

Looking forward to your response, and thanks for your patience.

Please feel free to let me know if you need further info.

[lbendlin]

Data = UNION(
    SELECTCOLUMNS(DataElectricity,"Cost",[Cost],"Date",[Date],"Points",[DBName-Points_Id],"Last Update",[Last_Update],"Source",[Source],"Units",[Units])
   ,SELECTCOLUMNS(DataGeneral,"Cost",[Cost],"Date",[Date],"Points",[DBName-Points_Id],"Last Update",[Last_Update],"Source",[Source],"Units",[Units])
   ,SELECTCOLUMNS(DataProfile,"Cost",BLANK(),"Date",[Date],"Points",[DBName-Points_Id],"Last Update",BLANK(),"Source",[Source],"Units",[Units])
   ,SELECTCOLUMNS(DataWater,"Cost",[Cost],"Date",[Date],"Points",[DBName-Points_Id],"Last Update",[Last_Update],"Source",[Source],"Units",[Units]))

Missing dates:

Missing = EXCEPT(SELECTCOLUMNS(Data,"Date",[Date]),SELECTCOLUMNS('Calendar',"Date",[Date]))

No dates are missing.

[sivarajan21]

Hi,

Many thanks for your guidance, this is really fantastic and appreciate your work quick response.

This was the start point i was looking and exactly you have given me this.

With regards to the 'Data' union measure,it is brilliant.But I need to add somethings to this measure.

currently measure works and when I filter down to a points level as shown below:

Here we need the Units column to be summarized(grouped) based on points,dates & source. for example shown in above screenshot,we have 5 rows for same points id and date. but what is expeceted would be below 

So those 5 rows has been aggregated to 2 rows one for invoice and direct. The negative units got cancelled during aggregation and 56.13+61.71=117.84. 

With regards to the 'Missing' measure, currently it uses only the date columns from both tables(Data, calendar). I think that is one reason it giving No dates are missing. But what i want from this measure to show,

For any given points level(Points column),within data table,we need to find out how many data table dates(col) are missing from the calendar table date.

Then we need to create a visual with the points id,missing dates and their missing count on a visual.For example below table achieved from a measure

FYI, I have attached the file with updated measure and relationship in below link:

Financial mang.pbix

Looking forward to your response, and thanks for your patience.

Please feel free to let me know if you need further info.

Thanks in advance.

[lbendlin]

So those 5 rows has been aggregated to 2 rows one for invoice and direct

Power BI aggregates automatically.  Remove the Last Update column from the visual

[sivarajan21]

Hi,

Many thanks for showing this visual that aggregates units and this works brilliantly. when we remove last update column and wow it works amazingly.

Please I  need your guidance on this.Now, with regards to the 'Missing' measure, currently it uses only the date columns from both tables(Data, calendar). I think that is one reason it is giving no missing dates. But what we want from this measure to show,

for any given points level(Points column),within data table,we need to find out how many data table dates(col) are missing from the calendar table date.if we aggregate to a points level, then surely we should have a missing dates. for example, as shown in below for illustration to give a clear info,

Then we need to create a visual(expected output) with the points id,missing dates and their missing count on a visual.For example below table achieved from a measure

If this is achieved, then i can close this query.

Looking forward to your response, and thanks for your patience.

Please feel free to let me know if you need further info.

[lbendlin]

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

[sivarajan21]

Hi,

My sincere apologise! 

I have 2 tables calendar table, and Data table. I have provided the sample data of these tables below:

Below is my sample Data table

DATA  

Date	Points	Source	Units	Last_Update	Cost
3/25/2021 12:00:00 AM	NorthYorksCC-2076	Direct	84.39	2/22/2023	0
3/25/2021 12:00:00 AM	NorthYorksCC-2076	Direct	84.39	2/22/2023	0
3/27/2021 12:00:00 AM	NorthYorksCC-2076	Direct	84.39	2/22/2023	0
6/28/2022 12:00:00 AM	INSE-1010	Invoice	0.0806	2/22/2023	0.16
6/30/2022 12:00:00 AM	INSE-1010	Invoice	0.0806	2/22/2023	0.16

Calendar table

Date

25-Mar-21

26-Mar-21

27-Mar-21

28-Mar-21

28-Jun-22

29-Jun-22

30-Jun-22

we need to find the missing date for each point id from data table by comparing it with calendar table date. for example, from the above sample data, if we look at the data table, 

the points (NorthYorksCC-2076) is missing the date 3/26/2021 12:00:00, but when we compare it with Calendar Date, it has 26-Mar-21. similarly, INSE-1010 is missing date 6/29/2022 12:00:00 AM  but calendar date, has 29-Jun-22.   I need to display these missing dates & their count in output. 

My expected outcome could be something like below:

points id	missing dates measure	no of missing dates measure
NorthYorksCC-2076	26/03/2021	1
INSE-1010	29/06/2022	1

we need one dax measure to display the above missing dates as a list to be shown on the report page.

we need another dax measure to display the count of those above missing dates.

The relationship between above tables are 

PFA pbix fiile FYR in below link:

Financial mang.pbix

Please feel free to let me know if you need further info

Thanks in advance