Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Greetings,
I'm struggling to find a solution to my problem. I have two tables outlined below, I want to create a measure that validates:
I'm struggling to find a way to compare the two sets of dates columns for each ID and return a 0 or 1. The requirement is to return the rows in the invoice table if there is a mismatch in the billing. My thoughts are to create a measure and filter on the measure when there is a mismatch. Please note the data sources uses direct query so limited in what I can do.
Any help would be much appreciated
Tables
Billing
ID | From Date | To Date |
1 | 31/10/17 | 31/12/18 |
2 | 31/10/17 | 11/11/19 |
3 | 31/10/17 | 30/11/17 |
Invoice
ID | From Date | To Date |
1 | 31/10/17 | 31/12/18 |
2 | 31/10/17 | 11/11/18 |
3 | 31/10/17 | 30/11/17 |
Required Result from Measure
ID | Measure result |
1 | 0 |
2 | 1 |
3 | 0 |
Solved! Go to Solution.
Thank you for your help,
I've managed to solve it now. I created three new variables, one to retrieve the billing ID, another for the invoice ID and lastly one that returns the value of billing where there s a match to invoice and it worked 🙂
Hi @Anonymous
try this code:
Measure1 =
VAR _BFD =
MAX ( Billing[From Date] )
VAR _BTD =
MAX ( Billing[To Date] )
VAR _IFD =
LOOKUPVALUE ( Invoice[From Date], Invoice[ID], MAX ( Billing[ID] ) )
VAR _ITD =
LOOKUPVALUE ( Invoice[To Date], Invoice[ID], MAX ( Billing[ID] ) )
RETURN
IF ( _BFD = _IFD && _BTD = _ITD, 0, 1 )
Output:
* add ID from the Billing table to your table.
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos✌️!!
Thanks for you reply,
This has been helpful and have tried it but having tried the following on my data:
ID | From Date | To Date |
2200059579046 | 31/10/2017 | 31/12/2018 |
1400074274632 | 31/10/2017 | 11/11/2019 |
1000021442273 | 31/10/2017 | 30/11/2017 |
2000029185068 | 31/10/2017 | 31/12/2018 |
1400069563915 | 31/10/2017 | 11/11/2019 |
1400011377608 | 31/10/2017 | 30/11/2017 |
1600080701673 | 31/10/2017 | 31/12/2018 |
1800027178380 | 31/10/2017 | 11/11/2019 |
2000099323350 | 31/10/2017 | 30/11/2017 |
2000061451305 | 31/10/2017 | 31/12/2018 |
1500057337836 | 31/10/2017 | 11/11/2019 |
2300013730962 | 31/10/2017 | 30/11/2017 |
1100033102735 | 31/10/2017 | 31/12/2018 |
Invoice:
ID | From Date | To Date |
2200059579046 | 31/10/2017 | 11/11/2018 |
1400074274632 | 31/10/2017 | 31/12/2018 |
1000021442273 | 31/10/2017 | 30/11/2017 |
2000029185068 | 31/10/2017 | 11/11/2018 |
1400069563915 | 31/10/2017 | 31/12/2018 |
1400011377608 | 31/10/2017 | 30/11/2017 |
1600080701673 | 31/10/2017 | 11/11/2018 |
1800027178380 | 31/10/2017 | 31/12/2018 |
2000099323350 | 31/10/2017 | 30/11/2017 |
2000061451305 | 31/10/2017 | 11/11/2018 |
Not sure what's going on and have spent a couple of hours unpicking it and am not sure why but it doe not seem to work.
@Anonymous
It works well on my computer:
Can you share your power bi file here after removing all sensitive data?
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Thanks @VahidDM,
I've dropped it here:
https://drive.google.com/file/d/1mT3HjoFkZPURJNg8H2ArP24PjmntaT_u/view?usp=sharing
Not sure what's going on 😞
Hi @Anonymous ,
I tried to download your shared file, but I have no access to it...
I created a sample pbix file(see attachment) base on your sample data, please check whether that is what you want.
1. Create a measure to get the flag to judge the match or not
Measure =
VAR _curid =
SELECTEDVALUE ( 'billing'[ID] )
VAR _invfdate =
CALCULATE (
MAX ( 'invoice'[From Date] ),
FILTER ( 'invoice', 'invoice'[ID] = _curid )
)
VAR _invtdate =
CALCULATE (
MAX ( 'invoice'[To Date] ),
FILTER ( 'invoice', 'invoice'[ID] = _curid )
)
VAR _billfdate =
CALCULATE (
MAX ( 'billing'[From Date] ),
FILTER ( 'billing', 'billing'[ID] = _curid )
)
VAR _billtdate =
CALCULATE (
MAX ( 'billing'[To Date] ),
FILTER ( 'billing', 'billing'[ID] = _curid )
)
RETURN
IF ( _billfdate = _invfdate && _billtdate = _invtdate, 0, 1 )
2. Apply the measure in visual/page/report level filter
If the above one still not working, could you please grant me the proper access to your shared file? Then I can check your file and provide a suitable solution. Thank you.
Best Regards
Thanks @Anonymous,
I've tried the method you have given me but still facing some issues. I have attached the file with full data in the link below.
I'm really struggling with DAX, the billing table is the master table, only IDs on here is valid. I'm creating a third table Date Mismatch to highlight from the invoice table where the to and from dates not do not match the billing, this is to flag that the invoice is wrong as the master data is from the billing.
However, solutions thus far are only pulling back the mismatches in IDs and the dates. Looking at my three tables below:
The first 11 rows in the portfolio and invoice table should show on the date mismatch table as a 1 in the measure which it is but it's also pulling back IDs not existing in the billing table. How do I filter out when the ID does not exist in the billing table but does in the invoice?
I've tried many things and spent half a day already trying different measures and var's but it's not working.
Here's the link to the file:
Any help would be greatly appreciated 🙏, as I'm losing the will to live with this 😒
Sorry and just to add, the intent is to use the measure to filter only records where the dates from the billing table do not match the invoice table
Thank you for your help,
I've managed to solve it now. I created three new variables, one to retrieve the billing ID, another for the invoice ID and lastly one that returns the value of billing where there s a match to invoice and it worked 🙂
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
76 | |
74 | |
57 | |
39 | |
33 |
User | Count |
---|---|
71 | |
66 | |
57 | |
49 | |
47 |