Get Current Month Sales Report using Power BI Measure

In this Power BI Tutorial, We will discuss how to get the current month sales 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 explain to you by taking a simple one 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.

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

Power BI current month report

To get the total number of product 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

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:

current month and previous month 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 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.

how to get current month 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: (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.

power bi get month from date

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.

dax filter current month

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:

current month sales in power bi

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:

current month power bi

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:
current month in power bi

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.

You may like following Power BI tutorials:

Hence in this Power BI Tutorial, We discussed how to get the current month 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.

>