Display Last N Month and Selected Month Data using Date Dimension in Power BI

Posted on by By Ramu Vudugula, in Power BI | 0

Requirement: In the Power BI report I have a Date dimension and Employee fact tables. In report I have added single select month slicer, year slicer and a line chart which should show month wise employee terminations count. Current report will look like below

Make data easy with Helical Insight.
Helical Insight is the world’s best open source business intelligence tool.

Power BI

As month is single select, Month over Month terms chart is showing data for the only selected month. But I want the last 3 months data along with the selected month in Month over Month terms chart

Ex: In the above image, month July is selected so chart should show data for April, May, June and July months

Solution:

  1. In the Power BI report we have Date dimension and Employee fact tables.
  2. Date dimension and Employee fact table are related with a date column
  3. In Date dimension we have a new column which is combination of both year and month in Month – Year format
  4. In Employee fact we have term column which will give the termination status of the employee
  5. Go to Employee fact table
  6. Click on create new measure
  7. Enter below expression
  8. Measure Terms = CALCULATE(SUM(Employee[term]), 	DATESINPERIOD('Date'[Date].[Date], MAX('Date'[Date].[Date]), -4, 	MONTH))
    
  9. In the above expression, we are calculating the total terms between the two date values
  10. As per the requirement we have to show last 3 months data along with selected month so I have used number 4 in the expression. (Based on user requirement change that value)
  11. Use the measure created above in the chart
  12. Now the chart will look like below

Make data easy with Helical Insight.
Helical Insight is the world’s best open source business intelligence tool.

Grab The 30 Days Free Trail

Output
Thank You
Ramu Vudugula
BI Developer
Helical IT Solutions Pvt Ltd

logo

Best Open Source Business Intelligence Software Helical Insight Here

logo

A Business Intelligence Framework


logo

Best Open Source Business Intelligence Software Helical Insight is Here

logo

A Business Intelligence Framework

0 0 votes
Article Rating
Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments