Show previous 6 months dynamically based on slicer selection

[Anonymous]

Hi All, 

I'm having trouble dynamically showing last 6 months of data in a bar graph, based on fortnight pay period selected. 

For example, if i select 15/04/2019, i want a bar graph showing the total headcount for the last 6 months. 

I used a solution i found and tailored it to me, but when i select a slicer value, it doesn't show 6 months prior to that. It just shows the headcount for whatever value was selected. If i unselect date slicer value, then it shows me 6 months (or 6 bars) of data. But i need it to show me previous 6 months based on selected slicer value. 

I think the problem is that i'm using calculated measures but i'm not 100% sure. Is there a way around this where i can show previous 6 months of data based on selected slicer value but have the actual output values be a calculated measure .

Previous 6 Months =

VAR MaxFactDate =

CALCULATE ( MAX ( StaffTable[Pay Period Ending] ), ALL ( 'DimDate' ) ) -

VAR FDate =

ENDOFMONTH ( 'DimDate'[Date] ) 

VAR Edate =

EDATE ( FDate, -6 ) -- get the last day of -N months

RETURN

IF (

MaxFactDate <= MAX ( 'DimDate'[Date] )

&& MaxFactDate > Edate,

CALCULATE ( SUM ( StaffTable[Headcount] ), ALL ( 'DimDate' ) )

Thanks All

[Anonymous]

-- Generally, you should not perform operations on dates
-- in your fact table. First of all. You should
-- always use the DimDate dimension to perform
-- any kind of time-based calculations.
-- Let's assume that DimDate[Date] is connected with 1:many
-- to StaffTable[Pay Period Ending] and DimDate is
-- marked as a Date table in the model.

-- This should be the first base measure:
[Total Headcount] = SUM ( StaffTable[Headcount] )

-- Then..
[Total Headcount (6M)] =
VAR __lastVisibleDate = MAX ( DimDate[Date] )
VAR __endOfLastVisibleMonth = ENDOFMONTH ( 'DimDate'[Date] ) 
VAR __firstDate = EDATE ( __endOfLastVisibleMonth, -6 )
var __timeIntervalOfInterest =
	DATESBETWEEN(
		DimDate[Date],
		__firstDate,
		__endOfLastVisibleMonth
	)
var __value =
	CALCULATE(
		[Total Headcount],
		KEEPFILTERS( __timeIntervalOfInterest )
	)
return
	__value

Best

Darek

[Anonymous]

Hi @Anonymous ,

My DimDate table is marked as a Date table and it's connected to the StaffTable using a 1:M relationship. However, when i select a date, it's still not showing me the previous 6 months on the bar chart. 

For example, if i 31st March 2019, I want it to show the headcount in a bar graph 6M prior to that so from Oct 2018 ot March 2019. But when i select it at the moment, it only shows the pay period selected. 

Here's the PBIX file for reference. 

Thank you!

[Anonymous]

That's because when you select something from your slicer, you also filter the dimension from which the slicer's values come. This is why you only see the dates that fall into the interval you've selected in your slicer. Just change the slicer to the variety called "Before Slicer". You can find it in the properties of the slicer. Looks like this:

The "Before" Slicer

The slicer you see always filters the dates from "the beginning of time" to the selected day. But your measure only shows values for the last 6 months. This is exactly what happens in the picture above, can you see?

Best

Darek