Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Show previous 6 months dynamically based on slicer selection

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    -- 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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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