Need help to get a min date value

[Samhunt]

Hello everyone, 

I have two tables 'Sales Data' and 'Accommodations'. 

I want to import the first date that an accommodation was first rented from the sales data table and put it in a new column in accommodations table. 

The column with the matching data is called  'accommodation' in both tables and the table with the dates its called 'date'

Thank you 

[123abc · Accepted solution]

Certainly! In Power BI, you can achieve this by creating a relationship between the 'Accommodations' table and the 'Sales Data' table based on the 'accommodation' column. Once you establish the relationship, you can use DAX (Data Analysis Expressions) to create a new column in the 'Accommodations' table that fetches the minimum date from the 'date' column in the 'Sales Data' table.

Here's a step-by-step guide:

1. Load Your Data: Ensure that both 'Accommodations' and 'Sales Data' tables are loaded into Power BI.

2. Create a Relationship: In the 'Relationships' view, create a relationship between the 'accommodation' column in the 'Accommodations' table and the 'accommodation' column in the 'Sales Data' table.

3. Create a New Column: In the 'Accommodations' table, go to the 'Model' view, select the table, and click on 'New column' in the 'Modeling' tab.

4. Write DAX Formula: Write a DAX formula to fetch the minimum date from the 'date' column in the 'Sales Data' table for each 'accommodation' in the 'Accommodations' table. Use the RELATEDTABLE and MINX functions. Here's an example:

DAX Measure:

MinDate = MINX(RELATEDTABLE('Sales Data'), 'Sales Data'[date])

1. This formula uses RELATEDTABLE to get a table of related 'Sales Data' rows for each 'Accommodation' and then uses MINX to find the minimum date in that table.

2. Apply Changes: After creating the new column, click on 'Apply Changes' to apply the changes to your data model.

Now, your 'Accommodations' table should have a new column ('MinDate') that contains the minimum date from the 'Sales Data' table for each 'accommodation'. You can use this column in your reports and visuals.

Remember to adjust column and table names according to your actual dataset.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[v-nuoc-msft · Accepted solution]

 Hi @Samhunt

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

Here are the data of the two tables：

'Sales Data'

'accommodations'

Make sure that there is a relationship between the two tables based on the 'accommodation' column. If there is no relationship between the two tables in the "Model View", you can create them manually, dragging the 'accommodation' field from one table to the 'accommodation' field in the other table.

Use the following DAX formula to calculate the minimum date:

First rented date = MIN('Sales Data'[date])

If there is no relationship between the two tables, you can also use a different DAX:

First rented date2 = CALCULATE(
    MIN('Sales Data'[date]),
    FILTER(
       'Sales Data',
        'Sales Data'[accommodation] = MAX('Accommodations'[accommodation])
    )
)

Here is the result

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

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.

[123abc · Accepted solution]

Certainly! In Power BI, you can achieve this by creating a relationship between the 'Accommodations' table and the 'Sales Data' table based on the 'accommodation' column. Once you establish the relationship, you can use DAX (Data Analysis Expressions) to create a new column in the 'Accommodations' table that fetches the minimum date from the 'date' column in the 'Sales Data' table.

Here's a step-by-step guide:

1. Load Your Data: Ensure that both 'Accommodations' and 'Sales Data' tables are loaded into Power BI.

2. Create a Relationship: In the 'Relationships' view, create a relationship between the 'accommodation' column in the 'Accommodations' table and the 'accommodation' column in the 'Sales Data' table.

3. Create a New Column: In the 'Accommodations' table, go to the 'Model' view, select the table, and click on 'New column' in the 'Modeling' tab.

4. Write DAX Formula: Write a DAX formula to fetch the minimum date from the 'date' column in the 'Sales Data' table for each 'accommodation' in the 'Accommodations' table. Use the RELATEDTABLE and MINX functions. Here's an example:

DAX Measure:

MinDate = MINX(RELATEDTABLE('Sales Data'), 'Sales Data'[date])

1. This formula uses RELATEDTABLE to get a table of related 'Sales Data' rows for each 'Accommodation' and then uses MINX to find the minimum date in that table.

2. Apply Changes: After creating the new column, click on 'Apply Changes' to apply the changes to your data model.

Now, your 'Accommodations' table should have a new column ('MinDate') that contains the minimum date from the 'Sales Data' table for each 'accommodation'. You can use this column in your reports and visuals.

Remember to adjust column and table names according to your actual dataset.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.