If date is greater than another date in another table

[tabuzahra]

Hello,

Kindly, I'm new to Power Bi and I would like to know how can I know if a date in a table is greater than another data in different table.

More clarifications:

I have a TimeSheet dashboard, and I have (Users Joining Date) in the table "Users" while I have the (Sheetdate) in the "TimeSheets" table and I would like to filter within a Sheetdate period, though I don't want the users that have joined before a sheetdate to appear in the filtered table.

[gathenjic]

Am replying to this one because its more ralated to the problem i have

I have 2 tables. Table1 for campaign monitoring which have date_monitored

Table 2: is for campaign calendar - it has Campaign_start_date, Campaign_end_date, Monitoring_start_date and Monitoring_end_date.

Moniroting done during campaign is considered as inprocess and after campaign end date its called EndProcess

My measure below is meant to Assign the type of monitoring based on dates when monitoring was done. Am however am getting error. I need guidance

Type IM = if(MIN('4344_Household Monitoring'[date_monitored])>=MIN('SIAS calendar'[SIAs Start Date]) && MIN('4344_Household Monitoring'[date_monitored])<=MIN('SIAS calendar'[SIAs End Date]),"Inprocess", if(MIN('4344_Household Monitoring'[date_monitored])>=MIN('SIAS calendar'[IM start Date]) && MIN('4344_Household Monitoring'[date_monitored])>=MIN('SIAS calendar'[IM End Date]),"End_process","ERROR"))

[PattemManohar]

@tabuzahra  Please provide some sample test data and your expected output, it will help to provide you an accurate solution.

[tabuzahra]

Thanks PattemManohar, below is a sample:

Timesheet table contains of: (UserID, Sheetdate, projectID, etc.) --> Sheetdate has a relationship with the general Calendar table

Users table contains of: (UserID, Joining Date, LastDay, and other users details)

What I need is to create a table to see which resources that haven't submitted their time sheet in a sepecified period (sheetdate), where this table should not show the dates before the users joined dates. Also, this table should not show the dates after the users Lastday dates.

In the below screen shot, the table shows all the resources according to the filtered period (sheetdate), though, the highlighted examples shouldn't appear in the table as the join date is after the sheetdate.

I hope this is clear enough, if you need more clarifications please let me know.

[v-yuezhe-msft]

@tabuzahra ,

According to your description, my understanding is that you want to filter the data whose Joining Date is before SgeetDate, and before LastDat.

In this scenario, we can create a measure to mark these rows which meet the requirement as 1, else as 0:

Measure = IF(MIN(User[Joining Date])<MIN(Timesheet[SheetDate]) && MIN(User[Joining Date])<MIN(User[LastDay]),1,0)

Then we can drag the measure to the visual level Fliters and set the measure is greater than 0.

The result will like below, please refer to the attached pbix.


Regards,
Lydia

[tabuzahra]

Hi Lydia,

Thanks alot. Though, the dax measure have worked partially, as it's not excluding the rows that are after the lastday as shown below in the below link:

https://drive.google.com/file/d/1nV_swcrhlZpGd-jso3slPCORD0GHR8M_/view?usp=sharing (if it didn't open with you, you can refer to the below table)

JoiningDate	LastDay	SheetDate	joined
1/1/2018 0:00	2/7/2019 0:00	2/3/2019 0:00	0
1/1/2018 0:00	2/7/2019 0:00	2/4/2019 0:00	0
1/1/2018 0:00	2/7/2019 0:00	2/5/2019 0:00	0
1/1/2018 0:00	2/7/2019 0:00	2/6/2019 0:00	0
1/1/2018 0:00	2/7/2019 0:00	2/7/2019 0:00	0
1/1/2018 0:00	2/7/2019 0:00	2/10/2019 0:00	0
1/1/2018 0:00	2/7/2019 0:00	2/11/2019 0:00	0
1/1/2018 0:00	2/7/2019 0:00	2/12/2019 0:00	0
1/1/2018 0:00	2/7/2019 0:00	2/13/2019 0:00	0
1/1/2018 0:00	2/7/2019 0:00	2/14/2019 0:00	0
1/1/2018 0:00	2/7/2019 0:00	2/17/2019 0:00	0
1/1/2018 0:00	2/7/2019 0:00	2/18/2019 0:00	0
1/1/2018 0:00	2/7/2019 0:00	2/19/2019 0:00	0
1/1/2018 0:00	2/7/2019 0:00	2/20/2019 0:00	0
1/1/2018 0:00	2/7/2019 0:00	2/21/2019 0:00	0
1/1/2018 0:00	2/7/2019 0:00	2/24/2019 0:00	0
1/1/2018 0:00	2/7/2019 0:00	2/25/2019 0:00	0
1/1/2018 0:00	2/7/2019 0:00	2/26/2019 0:00	0
1/1/2018 0:00	2/7/2019 0:00	2/27/2019 0:00	0
1/1/2018 0:00	2/7/2019 0:00	2/28/2019 0:00	0

[v-yuezhe-msft · Accepted solution]

@tabuzahra ,

Do you mean that you want to include rows whose Joining Date<= SheetDate <= LastDay?

In this scenario, we can change the measure as the following query:

Measure = IF(MIN(User[Joining Date])<=MIN(Timesheet[SheetDate]) && MIN(Timesheet[SheetDate])<=MIN(User[LastDay]),1,0)

Reagrds,
Lydia