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.
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 expecting to sell in the next month.
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
Read Power BI if date
Get the Next month Sales Report using Power BI DAX
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:
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 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:
- Export Power BI reports to PDF
- Microsoft Power BI Alerts
- Power BI Data Analysis Expressions (DAX) Tutorial
- Power BI Query Group Tutorial
- Power BI On-premises Data Gateway
- Export Power BI Reports to Excel
- Microsoft Power BI Error: This content isn’t available
- Power BI Buttons
- Power BI Change Data Type of a Column
- How to Split Columns in Power BI
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.
Hello Everyone!! I am Bhawana a SharePoint MVP and having about 10+ years of SharePoint experience as well as in .Net technologies. I have worked in all the versions of SharePoint from wss to Office 365. I have good exposure in Customization and Migration using Nintex, Metalogix tools. Now exploring more in SharePoint 2016 🙂 Hope here I can contribute and share my knowledge to the fullest. As I believe “There is no wealth like knowledge and no poverty like ignorance”