Earliest date from two fact tables as new query/parameter for date table

[DAST]

I need two new queries or parameter to compare and find the earliest and latest dates in the date columns of two fact tables. These two dates are then passed to the date table as its start and end points.

I know what the general structure should be but I don't know enough about M syntax and functions to write it out. I've tried various combinations of Table.AddColumn, List.Generate etc. to no avail. Here's how it looks in my head:

1. Create a list containing two values:
   • The earliest of column Date in table 1
   • The earliest of column Date in table 2
2. Select the earliest of the two dates.

A similar query would find the latest date of both columns. I can then point the date table at these two values/parameters and tell it to start and end at these dates.

Can anyone tell me how to write this in M?

[DAST]

Thanks both, good to know I was on the right lines - all I was missing was the curly brackets!

[BA_Pete · Accepted solution]

Hi @DAST ,

In Power Query, I think your parameter code should look something like this:

// Start Date
List.Min(
    {
        List.Min(Query1[DateColumn]),
        List.Min(Query2[DateColumn])
    }
)

// End Date
List.Max(
    {
        List.Max(Query1[DateColumn]),
        List.Max(Query2[DateColumn])
    }
)

Pete

[Akash_Varuna]

Try these
Create a query to find the earliest date by comparing the minimum dates from the Date columns in both fact tables.

Create another query to find the latest date by comparing the maximum dates from the Date columns in both fact tables.

Use these two queries as parameters (StartDate and EndDate) for the range in your date table.

Generate the date table dynamically by creating a continuous list of dates between the earliest and latest dates.