Forum Discussion
comparing dates in two tables
Good Afternoon
I am new to power bi and need some help please
I need to compare dates from two tables, event table and sign up table.
Event date and Sign Up date
Tables are joined many to one cardinality with single cross filter direction on a NamePk (name + Address)
The issue i have is that people can sign up to a course prior to the event and can sign up to multiple courses and i want to ascertain how many distinct people signed up to a course following the event.
I have the following to ascertain if a person has signed up to an event but this includes where the date is prior to the event date.
Thank you for your help
Hi,
In Table1, write this calculated column formula
App date = lookupvalue('Table 2'[Application date],'Table 2'[Name],'Table 1'[Name])
Write this measure and drag it to a card visual
Measure = countrows(filter(values('Table 1'[Name]),'Table 1'[Event_date]>='Table 1'[App date]))
Hope this helps.
5 Replies
Hi Anonymous,
Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hi see below example of what is in each table. The name will only appear once in table one but can appear multiple times in table 2.
Table 1 Event Date Name Address Area Interested in NamePK 10/10/2022 aa aaaaa apprentice aa - aaaaa 10/10/2022 bb bbbbb apprentice bb - bbbbb 10/10/2022 cc ccccc a level cc - ccccc 10/10/2022 dd ddddd a level dd - ddddd Table 2 Application Date Name Address Course Code NamePK 11/10/2022 aa aaaaa 1 aa - aaaaa 11/10/2022 aa aaaaa 2 aa - aaaaa 20/10/2022 bb bbbbb 1 bb - bbbbb 10/10/2022 cc ccccc 1 cc - ccccc 10/10/2022 cc ccccc 25 cc - ccccc 09/10/2022 ee eeeee 3 ee - eeeeee Hi,
In Table1, write this calculated column formula
App date = lookupvalue('Table 2'[Application date],'Table 2'[Name],'Table 1'[Name])
Write this measure and drag it to a card visual
Measure = countrows(filter(values('Table 1'[Name]),'Table 1'[Event_date]>='Table 1'[App date]))
Hope this helps.
Hi please see below sample data
event date application date customer id course id 12/10/2022 12/10/2022 1 1 12/10/2022 13/10/2022 1 2 12/10/2022 25/09/2022 2 1 12/10/2022 12/10/2022 3 1 12/10/2022 15/10/2022 4 2 12/10/2022 01/10/2022 5 1 12/10/2022 13/10/2022 5 2 12/10/2022 12/10/2022 6 1 12/10/2022 11/10/2022 6 3 12/10/2022 12/10/2022 7 3 12/10/2022 13/10/2022 8 4 What i want to count is how many people (distinct count) who attended the event applied either on the same day of the event or the following day.
event date and application date are in two separate tables, course id is in the application table and customer id is in both.
Tables are joined many to one cardinality with single cross filter direction on a NamePk (name + Address)
Hi,
Share the different tables that you have. Else, share the download link of your PBI file.