Get Next Month Sales Report in Power BI

In this Power BI Tutorial, We will discuss how to get a total sales report for next month using Power BI DAX rule.

Also, We will see how to create a Calendar Date table, How to make a relationship between two tables and manage the relationship. All these things I will describe to you by taking a simple one requirement.

In my previous tutorial, we discussed how to get the Current Month Sales Report using Power BI Measure.

Requirement:

As per my requirement, I have a Sales table in my Power BI Desktop. This table has many different data types of columns.

It has a Bill Date column whose data type is Date/Time. Similarly, It has another column named as Total Product whose data type is Number. You can see my Sales List in the below screenshot.

get next month in power bi

This Sales list is having many items with different dates, months and years with MM/DD/YYYY format. Now, What I have to do is, In between all those months, I want to get the total number of products which are expecting to sell in the next month.

Solution:

To get the total number of products which is expecting to sell in the next month, You have to do the below things as:

  • Create a Date calendar
  • Create four new columns in the Date calendar and apply the rules for those (month, monthdate, monthnumber, year)
  • Format the data type of Bill Date as Date
  • Create a new column in the Sales table to format the Bill Date
  • Make the relationship between two dates of both New Column and Date table
  • Apply Next month formula using Power BI Measure
  • Test the Measure by taking Card from the Visualization

Get the Next month Sales Report using Power BI DAX

Step-1:

First of all, Open your Power BI Desktop and Sign in with your Microsoft account. Get the SharePoint List from SharePoint Online Site to your Power BI Desktop.

If you want to know how to get the SharePoint List from SharePoint Online Site to Power BI Desktop, then you can refer this link: Get SharePoint List to Power BI Desktop

You can see my Sales table in the Power BI Desktop as shown below:

Get Next Month Sales in Power BI

Step-2: (Create Date Calendar)

In the Power BI Desktop, create a new table and put the below formula which is used to create a Date Calendar.

Date = CALENDAR (DATE(2000,1,1), DATE(2090,12,31))

Once you create the Date table, you need to create five new columns in the Date calendar and apply the below rules to each new column.

  • Month = format(‘Date'[Date],”MMMM”)
  • MonthDate = STARTOFMONTH(‘Date'[Date])
  • monthNumber = month(‘Date'[Date])
  • StartOfMonth = STARTOFMONTH(‘Date'[Date])
  • Year = year(‘Date'[Date])

After creating the Date Table and its five new columns, it will appear like the below screenshot.

get next month sales in power bi dax

Step-3: (Format the data type of Bill Date as Date)

Now you have to format the data type of [BillDate] column. To change the data type, click on the ‘Sales'[BillDate] and go to the Modelling tab and select the data type as Date from the Data type section.

Similarly, Change the data type of ‘Sales'[TotalProduct] as the Whole number.

If you want to know the more details about the Data type, then click on this link: Power BI Data type

Step-4: (Create a new column in the Sales table to format the Bill Date)

Here in the Sales table, you need to create a new column to format the Bill date and put the below formula:

Column = DATE(YEAR(Sales[BillDate]),MONTH([BillDate]),DAY(Sales[BillDate]))

Step-5: (Make the relationship between two dates of both New Column and Date table)

After creating the new column to format the Bill date, you need to make a relationship between two dates of both the New Column and Date table.

For that purpose, In Power BI Desktop Page, Go to the Model View which is present on the left side. Once you will click on the Model view, the below screen will appear.

how to get next month in power bi

Now, you need to join the Sales[Column] to Date[Date]. For that, just drag the ‘Sales'[Column] onto Date[Date].

Once both tables joined, then the relationship will look like the below screenshot.

next month in powerbi

Step-6: (Apply Next month formula using Measure)

At last, You have to apply the rule to get the total number of products that are expecting to sell for next month. For this, again go to the Report view (from the left side of the page in Power BI Desktop page) and create a New measure in the Sales table.

Once you have created the new measure, apply the below formula to get the next month:

Next Month Sales = 
CALCULATE (
    SUM ( Sales[TotalProduct] ),
    FILTER (
        ALL ( 'Date' ),
        'Date'[StartOfMonth]
            = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 )
    ) 
)
Power BI next month

Step-6: (Test the measure for Next month)

After completing all the steps, Don’t forget to test your requirements. For testing purposes, Click on the Card from Visualizations section.

Just drag and drop the Measure(Next Month Sales) to Fields section of the Card. When you put the measure into the fields section, then you can see your total product values for the Next month as shown below:

Power BI next month sales

As my current month is November, So it is displaying the total number of Products which are expecting to sell in December is 15. On the left side, I have taken a table for reference so that you can understand easily.

You may like following Power BI tutorials:

Hence in this Power BI Tutorial, We discussed how to get the next month’s sales report using Power BI DAX rule.

Also, We saw how to create a Calendar Date table, How to make a relationship between two tables and manage the relationship in Power BI Desktop by using the above requirement.

>