Get Current Month Sales Report using Power BI Measure

In this Power BI Tutorial, We will discuss how to get the current month using the 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. Furthermore, we will get to know what is the process to get Current Month Sales Report using Power BI by taking a simple requirement.

Requirement:

As per my requirement, I have a Sales table on 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.

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 sold in the current month.

Power BI current month report

To get the total number of products in the current 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
• Make the relationship between two dates of both tables (Sales and Date table)
• Apply current month formula using Power BI Measure
• Test the Measure by taking Card from the Visualization

Read Power BI if date

Get Current month 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 as shown below:

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 Table = CALENDAR(date(2019,1,1),DATE(2019,12,31))

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

• Month = ‘Date Table'[Date].[Month]
• MonthDate = STARTOFMONTH(‘Date Table'[Date])
• monthNumber = ‘Date Table'[Date].[MonthNo]
• Year = ‘Date Table'[Date].[Year]

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

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: (Join two Dates of both tables (Sales and Date table))

After changing the data type of Sales[BillDate] as Date, you need to make a relationship between two dates of both tables (Sales 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.

In the above screen, ignore the Budgets list. Now, you need to join the Sales[BillDate] to Date Table[Date]. For that, just drag the ‘Sales'[BillDate] onto Date Table[Date].

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

Step-5: (Apply current month formula using Measure)

At last, You have to apply the rule to get the total number of products for the current month. For this purpose, again go to Report view and create a New measure in the Sales table.

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

Sales for current month = CALCULATE (SUM (Sales[TotalProduct]), FILTER (        ALL ('Date Table'), 'Date Table'[MonthDate]= DATE (YEAR ( TODAY ()), MONTH (TODAY () ) , 1 )))

Also, you can refer the measure formula from the below screenshot:

Step-6: (Test the measure for Current 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(Sales for the current month) to Fields section of the card. When you put the measure into the field section, then you can see your total product values of the current month as shown below:

As my current month is November, So it is displaying the total number of product sales as 5. I have taken a table for reference so that you can understand easily.

After doing all these things, if you got your result as per your requirement, then it is well and good. But here I would like to share one thing.

In some cases, after doing all these things also, it is not working perfectly and it is giving the wrong value which should not be. To avoid this type of issue, remember these below points which you have to do:

• Create a New column in the Sales table and apply the below formula as:

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

• Now make a relationship between this Sales[Column] with the Date Table[Date] as you made previously. Go to the Model view and just drag the ‘Sales'[Column] onto Date Table[Date].
• After making the relationship between both tables, it will look like below screenshot:

Now again come back to the Report view, Once you will refresh the data, then you can see the proper result on the Card as per your requirement.

Get Next Month Sales Report in Power BI

Let us see, how to get a total sales report for next month using the 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 requirement.

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.

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 expected to sell in the next month.

Solution:

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

• 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

Read Power BI if date

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 the SharePoint Online Site to your Power BI Desktop.

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

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.

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.

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.

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 )
    ) 
)

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:

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

How to get Current Month Sales Report using Power BI

Let us see how we can get the current month’s sales data using Power BI Measure in Power BI,

• In this example, I am going to use the financial data to calculate the current month’s sales value using Power BI DAX.
• Load the data to the Power BI desktop using the get data option. Then create a new measure and apply the below formula to find the current month’s value.

Current Month = max('financials'[Date].[Month])

where,

1. Current Month = Measure Name
2. financials = Table Name
3. Date = Column Name

• Format the current Month’s measure value, so that it will not show the data type error, if we directly call the current month value without formatting it will show an error message.
• Create another measure to format the current Month value, create a new measure and apply the below formula to format the current month’s value.

Format current Month value = VAR year_month = format(financials[Current Month],"MMMyyyy")
  RETURN ROW (
    "year_month", Year_Month)

where,

1. Format current Month value = Measure Name
2. year_month = Variable Name
3. financials = Table Name
4. Current Month = Previously calculated measure name

• Now sum the total value of sales and Profit and store them in another measured value, create a new measure and apply the below formula to sum the two different columns’ values.

Total Sales = SUMX(financials , financials[ Sales]+financials[Profit])

Where,

1. Total = Measure Name
2. financials = Table Name
3. Sales & Profit = Columns Table

• To calculate the current month’s sales value, Create a new Measure and apply the below-mentioned formula:

sales for current month = 
var Current_Month = financials[Format current Month value]
return
CALCULATE (
    financials[Total Sales],
    FILTER (
        ALL ( financials[Date].[Month] ),
        financials[Date].[Month] = Current_Month
    )
)

• Once the Values are calculated, select the card visual and drag and drop the calculated Measure in it. It will display the current month’s value.
• In the below screenshot, you can see that Card visual displays the current Month’s name(ie, September Month), Total sales value, and current month’s sales value.

This is how to get the current month’s sales data using Power BI Measure in the Power BI,

You may like the following Power BI tutorials:

• Export Power BI reports to PDF
• Microsoft Power BI Alerts
• Export Power BI Reports to Excel
• Power BI Change Data Type of a Column
• Power BI Query Group Tutorial
• How to Split Columns in Power BI
• Power BI Error: This content isn’t available

Hence in this Power BI Tutorial, We discussed how to get the current month using the 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. Additionally, we saw the process to get Current Month Sales Report using Power BI DAX by taking a simple requirement.