Comparing Dates in two Fact Tables

Question

Hi there,&nbsp;Wondering if anyone can help.&nbsp;My structure is thus: Students table, one to many relationship to Registration table and Induction table.&nbsp;&nbsp;Scenario is I have to calculate the % of students I have in my Students Table, who have both a Registration Record in the Registration Table and an Induction record in the Induction Table (where the 'Induction Type' field has a specific value of 'Event'), and where the Date of Registration (Registration Table) is before or equal to the Date of Induction (Induction Table).&nbsp;&nbsp;How can I do this simply? I have done this with a massive workaround because I am not the best with Dax.&nbsp;Jihwan_Kim&nbsp;can you help?

jihwan_kim · Answer

Hi,&nbsp;Gingerjeans88&nbsp;Please share your sample pbix file's link here, together with how your desired outcome looks like. Then I can try to look into it to come up with an accurate solution.Thank you.

gingerjeans88 · Answer

Hey&nbsp;Jihwan_Kim&nbsp;- thanks so much for replying! Getting quite desperate so really appreciate the help.&nbsp;&nbsp;I haven't had the time to create a PBIX but I have mocked up the data in excel, showing how the induction and registration records are linked to Students via Student ID.&nbsp;&nbsp;The dataset is here -&nbsp;https://www.dropbox.com/scl/fi/zs4xb267u59v360kqwe8l/Dataset.xlsx?dl=0&amp;rlkey=bxwvyesnnx0t4rny8bnygkzrp&nbsp;&nbsp;So grateful for any help!

ashish_mathur · Answer

Hi,
Please also show the expected result there.

jihwan_kim · Answer

Hi,&nbsp;Gingerjeans88&nbsp;I am not quite sure how your desired outcome looks like, but please check the below picture and the sample pbix file's link down below whether it is what you are looking for.All measures are in the sample pbix file.&nbsp;&nbsp;https://www.dropbox.com/s/dk4t27zxg31ixj2/gingerjeansv2.pbix?dl=0&nbsp;&nbsp;&nbsp;Hi, My name is Jihwan Kim. &nbsp;If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up. &nbsp;Linkedin: linkedin.com/in/jihwankim1975/Twitter: twitter.com/Jihwan_JHKIM

gingerjeans88 · Answer

Thank you SO much for the help Jihwan_Kim&nbsp;!!! The measure is not quite there.&nbsp;&nbsp;I need to count the number of students in the student table where the date on the registration record is before the date on the induction record (and obviously that the date isn't just blank). In the sample data I gave you, it should be 4 out of the 6 students in that student table.I forgot to include the extra field on the registration table for 'registration type', but it would be an extra filter on that.&nbsp;So how many students have an earlier registration than induction, where the registration type = XYZ.&nbsp;I also need it the other way around, so how many students have an earlier induction thant registration.

Forum Discussion

Comparing Dates in two Fact Tables

7 Replies

Related content

Table Sort , sort order , comparer

Compare 2 Tables

Date Slicer not Responsive to Calculated Date Table

Compare two date columns from different tables

Compare 2 tables