STARTOFMONTH

How to Use the Power BI DAX function STARTOFMONTH

In this article, we will explore how to use the STARTOFMONTH function in Power BI, including its syntax, parameters, and examples of how it can be used in real-world scenarios.

Syntax of the STARTOFMONTH Function

The syntax for the STARTOFMONTH function is as follows:


STARTOFMONTH(❰date❱)


The ❰date❱ parameter represents the date for which you want to retrieve the first day of the month. The ❰date❱ parameter can be a column reference, a date literal, or a function that returns a date.

Examples of Using the STARTOFMONTH Function

Let's take a look at some examples of how to use the STARTOFMONTH function in Power BI.

Example 1: Calculate Monthly Totals

Suppose you have a sales table that contains a Date column and a Sales column. You want to calculate the total sales for each month. You can use the STARTOFMONTH function to retrieve the first day of each month and then group the sales by month.

Here's the DAX formula to achieve this:


Total Sales by Month =

SUM(Sales[Sales])

GROUP BY STARTOFMONTH(Sales[Date])


Example 2: Filter Data by Month

Suppose you want to filter a table to show only the data for a specific month. You can use the STARTOFMONTH function to retrieve the first day of that month and then use the FILTER function to filter the table based on that date.

Here's the DAX formula to achieve this:


Filtered Sales by Month =

FILTER(Sales,

Sales[Date] ❱= STARTOFMONTH(DATE(2021, 1, 1)) &&

Sales[Date] ❰= EOMONTH(DATE(2021, 1, 1), 0)

)


This formula filters the Sales table to show only the data for January 2021.

Example 3: Create Custom Time Periods

Suppose you want to create a custom time period that spans multiple months, such as a fiscal quarter or a school semester. You can use the STARTOFMONTH function to retrieve the first day of the starting month and then use the DATEADD function to add the appropriate number of months.

Here's the DAX formula to achieve this:


Fiscal Quarter Sales =

CALCULATE(SUM(Sales[Sales]),

DATESBETWEEN(

Sales[Date],

STARTOFMONTH(DATE(2021, 1, 1)),

DATEADD(STARTOFMONTH(DATE(2021, 1, 1)), 2, MONTH)

)

)


This formula calculates the total sales for the first quarter of the fiscal year, which starts in January.

The STARTOFMONTH function is a useful tool for working with dates in Power BI. It allows you to retrieve the first day of the month for a given date and create custom time periods, filter data, and calculate monthly totals. By understanding the syntax and examples of using the STARTOFMONTH function, you can take your data analysis to the next level in Power BI.

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

Upcoming Courses

11-13 November 2024

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)