Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
tabuzahra
Helper II
Helper II

If date is greater than another date in another table

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.

1 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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
gathenjic
Frequent Visitor

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
Community Champion
Community Champion

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





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Thanks 

 

 

 

 

 

@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.
1.PNG

 

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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)

 

JoiningDateLastDaySheetDatejoined
1/1/2018 0:002/7/2019 0:002/3/2019 0:000
1/1/2018 0:002/7/2019 0:002/4/2019 0:000
1/1/2018 0:002/7/2019 0:002/5/2019 0:000
1/1/2018 0:002/7/2019 0:002/6/2019 0:000
1/1/2018 0:002/7/2019 0:002/7/2019 0:000
1/1/2018 0:002/7/2019 0:002/10/2019 0:000
1/1/2018 0:002/7/2019 0:002/11/2019 0:000
1/1/2018 0:002/7/2019 0:002/12/2019 0:000
1/1/2018 0:002/7/2019 0:002/13/2019 0:000
1/1/2018 0:002/7/2019 0:002/14/2019 0:000
1/1/2018 0:002/7/2019 0:002/17/2019 0:000
1/1/2018 0:002/7/2019 0:002/18/2019 0:000
1/1/2018 0:002/7/2019 0:002/19/2019 0:000
1/1/2018 0:002/7/2019 0:002/20/2019 0:000
1/1/2018 0:002/7/2019 0:002/21/2019 0:000
1/1/2018 0:002/7/2019 0:002/24/2019 0:000
1/1/2018 0:002/7/2019 0:002/25/2019 0:000
1/1/2018 0:002/7/2019 0:002/26/2019 0:000
1/1/2018 0:002/7/2019 0:002/27/2019 0:000
1/1/2018 0:002/7/2019 0:002/28/2019 0:000

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.