Forum Discussion
How to Assign text value based on dates while the date variable are in 2 different tables
Hi gathenjic ,
Best option is to create a calendar table to connect both tables and then use that one to make the filtering you need.
If you can share a sample data and expected result would be more helpfull to guide you in the right direction.
MFelix Thanks for getting intouch to SOLVE
How to I attached the .pbix file?
Below is how my table structure looks
Table 1: HH-Monitoring - This is the data collection form. Activities are monitored as they happen (Inprocess) and after completion (End_process)
| Province1 | District2 | 07/02/2020 |
| Province1 | District2 | 08/02/2020 |
| Province1 | District2 | 09/02/2020 |
| Province1 | District2 | 10/02/2020 |
Activity Calendar: Showing dates when Activity started and Ended (inprocess) and dates of post campaign activities
| Region | District | Response_Type | Activity_Start_Date | Activity_End_Date | Post_start_Date | Post_End_Date |
| Province1 | District1 | Round 1 | 07/02/2020 | 11/02/2020 | 12/02/2020 | 15/02/2020 |
| Province1 | District2 | Round 1 | 07/02/2020 | 11/02/2020 | 12/02/2020 | 15/02/2020 |
| Province1 | District3 | Round 1 | 07/02/2020 | 11/02/2020 | 12/02/2020 | 15/02/2020 |
| Province1 | District4 | Round 1 | 07/02/2020 | 11/02/2020 | 12/02/2020 | 15/02/2020 |
| Province5 | District5 | Round 1 | 07/02/2020 | 11/02/2020 | 12/02/2020 | 15/02/2020 |
| Province6 | District6 | Round 1 | 07/02/2020 | 11/02/2020 | 12/02/2020 | 15/02/2020 |
| Province6 | District7 | Round 1 | 07/02/2020 | 11/02/2020 | 12/02/2020 | 15/02/2020 |
| Province1 | District1 | Round 2 | 14/03/2020 | 18/03/2020 | 20/03/2020 | 25/03/2020 |
| Province1 | District2 | Round 2 | 14/03/2020 | 18/03/2020 | 20/03/2020 | 25/03/2020 |
| Province1 | District3 | Round 2 | 14/03/2020 | 18/03/2020 | 20/03/2020 | 25/03/2020 |
Table 3: List of districts for table relationships purpose
| PROVINCE | DISTRICT |
| Province1 | District1 |
| Province1 | District2 |
| Province1 | District3 |
| Province1 | District4 |
| Province5 | District5 |
| Province6 | District6 |
| Province6 | District7 |
Expectation: To use look at the date of Monitoring against the SIAs calendar and classfy as inprocess, end process otherwise if not within those dates its test data for training
Hi,
Could you please tell me that why the column in your formula such as [SIAs Start Date] or [SIAs End Date]does not exist in your sample table?
Please share the expected result as a screenshot for me to help you further.
You can share your pbix file by OneDrive for Business if convenient.
And remember to remove any sensitive data.
Thanks.
Best Regards,
Giotto Zhi
In preparing a sample pbix, I renamed the SIAs start and end date to activity start and End date. In the pbix file shared i have a measure that i was string to create for calculation. maybe it will give more clarity
Below is the expected output
Expected Output: bases on Monitotin_date and Activity dates and IM dates
Hi,
It is an incorrect use of IF function that caused error, please try this:
TYpe of Monitoring = IF ( MIN ( HH_Monitoring[date_monitored] ) >= MIN ( 'Activity Calendar'[SIAs_Start_Date] ) && MIN ( HH_Monitoring[date_monitored] ) <= MIN ( 'Activity Calendar'[SIAs_End_Date] ), "IN_Process", IF ( MIN ( HH_Monitoring[date_monitored] ) >= MIN ( 'Activity Calendar'[IM_start_Date] ) && MIN ( HH_Monitoring[date_monitored] ) <= MIN ( 'Activity Calendar'[IM_End_Date] ), "END_Process", "TestData" ) )The result shows:
Best Regards,
Giotto Zhi
