Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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.
Solved! Go to Solution.
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
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
@tabuzahra Please provide some sample test data and your expected output, it will help to provide you an accurate solution.
Proud to be a PBI Community Champion
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.
@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
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 |
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
91 | |
87 | |
84 | |
67 | |
49 |
User | Count |
---|---|
131 | |
110 | |
96 | |
70 | |
67 |