Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Mike1309
Frequent Visitor

Data grouping based on second table

Dear Experts,

I'm looking for a solution to a problem I cannot deal with 

I have two tables: Periods: 

Mike1309_2-1702560117709.png

 

and invoices:

Mike1309_1-1702560078078.png

 

every period for every country is different

Based on country and invoice date I would like to know to which period from the "period" table the invoice belongs to 

I think I need your help as I have no idea how to deal with

 

 the result should be:

if the user chooses the country  and period should get a list of invoices 

Thank you for your help in advance 

Mike

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Mike1309

 

I have understood about your problem, here is the solution I offer:

 

Here are the data of the two tables, you can check the date type, here it is recommended to use the (m/d/yyyy) format

vnuocmsft_0-1702626941239.png

vnuocmsft_1-1702626955095.png

vnuocmsft_2-1702626977478.png

 

Create a  measure, you can match the period by judging the country name and date range.

vnuocmsft_0-1702627056923.png

 

 

result = var invoiceDate = SELECTEDVALUE(invoices[invoice date])
 var invoiceCountry = SELECTEDVALUE(invoices[country])

 RETURN CALCULATE(
        MAX(periods[period]),
    FILTER(periods, 
        periods[country] = invoiceCountry && 
        periods[start day] <= invoiceDate &&
        periods[end day] >= invoiceDate))

 

 

Here is the result, and you can add slicers to filter

vnuocmsft_0-1702627166002.png

vnuocmsft_0-1702627231677.png

Best Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

 

 

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hi @Mike1309

 

I have understood about your problem, here is the solution I offer:

 

Here are the data of the two tables, you can check the date type, here it is recommended to use the (m/d/yyyy) format

vnuocmsft_0-1702626941239.png

vnuocmsft_1-1702626955095.png

vnuocmsft_2-1702626977478.png

 

Create a  measure, you can match the period by judging the country name and date range.

vnuocmsft_0-1702627056923.png

 

 

result = var invoiceDate = SELECTEDVALUE(invoices[invoice date])
 var invoiceCountry = SELECTEDVALUE(invoices[country])

 RETURN CALCULATE(
        MAX(periods[period]),
    FILTER(periods, 
        periods[country] = invoiceCountry && 
        periods[start day] <= invoiceDate &&
        periods[end day] >= invoiceDate))

 

 

Here is the result, and you can add slicers to filter

vnuocmsft_0-1702627166002.png

vnuocmsft_0-1702627231677.png

Best Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

 

 

Tom_Y
Advocate II
Advocate II

All you need is a "Date Table". Search google/ youtube. It's not very difficult.

1. Make a Date Table.

2. Link your 2 tables to that Date Table.

3. Add a Slicer to your matrix, something look like this one, allowing users to choose "Period" and "Country".

Tom_Y_0-1702589752933.png

 

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.