Forum Discussion

gathenjic's avatar
gathenjic
Frequent Visitor
6 years ago

How to Assign text value based on dates while the date variable are in 2 different tables

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_...
Data Protection
MFelix's avatar
MFelix
Super User
6 years ago

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.

gathenjic's avatar
gathenjic
Frequent Visitor
6 years ago

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)

 

Province1District207/02/2020
Province1District208/02/2020
Province1District209/02/2020
Province1District210/02/2020

 

Activity Calendar: Showing dates when Activity started and Ended (inprocess) and dates of post campaign activities

RegionDistrictResponse_TypeActivity_Start_DateActivity_End_DatePost_start_DatePost_End_Date
Province1District1Round 107/02/202011/02/202012/02/202015/02/2020
Province1District2Round 107/02/202011/02/202012/02/202015/02/2020
Province1District3Round 107/02/202011/02/202012/02/202015/02/2020
Province1District4Round 107/02/202011/02/202012/02/202015/02/2020
Province5District5Round 107/02/202011/02/202012/02/202015/02/2020
Province6District6Round 107/02/202011/02/202012/02/202015/02/2020
Province6District7Round 107/02/202011/02/202012/02/202015/02/2020
Province1District1Round 214/03/202018/03/202020/03/202025/03/2020
Province1District2Round 214/03/202018/03/202020/03/202025/03/2020
Province1District3Round 214/03/202018/03/202020/03/2020

25/03/2020

 

Table 3: List of districts for table relationships purpose

PROVINCEDISTRICT
Province1District1
Province1District2
Province1District3
Province1District4
Province5District5
Province6District6
Province6District7


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