Data grouping based on second table

[Mike1309]

Dear Experts,

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

I have two tables: Periods: 

and invoices:

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

[Anonymous · Accepted solution]

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

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

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

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]

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".