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

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

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

Measure Terms = CALCULATE(SUM(Employee[term]), DATESINPERIOD(‘Date’[Date].[Date], MAX(‘Date’[Date].[Date]), -4, MONTH))

8. In the above expression, we are calculating the total terms between the two date values

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

10. Use the measure created above in the chart

11. Now the chart will look like below