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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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