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.
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 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
Read Power BI if date
Get Current month Report using Power BI DAX
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.
You may like 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 Buttons
- Power BI Workbooks
- Power BI Error: This content isn’t available
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.
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”