FIRSTDATE

How to Use the Power BI DAX function FIRSTDATE

What is the FIRSTDATE function?

The FIRSTDATE function in DAX returns the first date in a given column. This function is useful when you need to find the first date in a time series or when you want to group data by the first date in a column. The syntax for the FIRSTDATE function is as follows:

FIRSTDATE(❰column❱)

where `❰column❱` is the name of the column that contains the dates.

How to use the FIRSTDATE function in Power BI

To use the FIRSTDATE function in Power BI, you need to follow these steps:

1. Open Power BI Desktop and create a new report.

2. Add a data source to your report by clicking on the "Get Data" button on the Home tab.

3. Select the data source you want to use and click on the "Connect" button.

4. Once the data is loaded, go to the "Fields" pane and select the table that contains the date column you want to use.

5. Drag the date column into the "Values" section of the visualizations pane.

6. Click on the "Visualizations" button on the Home tab and select the type of visualization you want to use.

7. Once the visualization is created, go to the "Visualizations" pane and select the "Format" tab.

8. Scroll down to the "Data colors" section and click on the "Color by rules" button.

9. In the "Color by rules" window, select "Field value" from the drop-down menu and choose the date column you want to use.

10. In the "Data colors" section, click on the "Advanced controls" button.

11. In the "Advanced controls" window, click on the "Add rule" button.

12. In the "Add rule" window, select "Less than or equal to" from the drop-down menu and enter the following formula in the text box:

FIRSTDATE(❰date column❱)

where `❰date column❱` is the name of the date column you want to use.

13. Click on the "OK" button to apply the rule.

14. Repeat steps 11-13 for each date range you want to use.

Examples of using the FIRSTDATE function

Let's look at some examples of using the FIRSTDATE function in Power BI.

Example 1: Finding the first date in a time series

Suppose you have a table that contains sales data for different products and you want to find the first date in the time series. To do this, you can use the FIRSTDATE function as follows:

FIRSTDATE(Sales[Date])

where `Sales` is the name of the table and `Date` is the name of the column that contains the dates.

Example 2: Grouping data by the first date in a column

Suppose you have a table that contains sales data for different products and you want to group the data by the first date in the time series. To do this, you can use the FIRSTDATE function in combination with the GROUPBY function as follows:

GROUPBY(Sales, FIRSTDATE(Sales[Date]), “Total Sales”, SUM(Sales[Sales]))

This formula groups the sales data in the `Sales` table by the first date in the `Date` column and calculates the total sales for each group.

The FIRSTDATE function in DAX is a powerful tool for manipulating dates in Power BI. It allows you to find the first date in a time series, group data by the first date in a column, and perform other calculations on dates. By following the steps outlined in this article, you can easily use the FIRSTDATE function in your Power BI reports.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)