Forum Discussion

Mike1309's avatar
Mike1309
Frequent Visitor
2 years ago
Solved

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: 

 

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's avatar
    Anonymous
    2 years ago

    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.

     

     

2 Replies

  • Tom_Y's avatar
    Tom_Y
    Advocate II
    2 years ago

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

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.