Display last n periods on a chart selecting last period on slicer

[Vladisam]

Hi community,

I am trying to build a report that will show number of active employees for last N periods with selection of single (last) period being done on a slicer. Last N periods are defined with "What-If" slicer (see the linked file).

I have 2 problems:

1. I am using DATESINPERIOD but it seems to be not working:

Actives (last n months) = 
CALCULATE (
    [Actives],
    DATESINPERIOD ( dimCalendar[Date], MAX ( dimCalendar[Date] ), -N[N Value], MONTH )
)+10

(I added 10 just to see two lines on a chart, when I put original measure (Actives) and the one for last n months expecting to see it just for specified period)

     2. I used slicer for the date range, as with selection of single date (intended to be last reported date) chart shows only 1 data point instead of N periods - how do I implement single selection?

Here is the model:

PBIX file 

Vlad

[Vladisam]

Thanks @pranit828 

I would like to provide a user a flexibility of choosing of number of periods they want to see via "What-if" slicer. Relative date slicer works for sure, but is a bit too manual and I want to avoid that.  

I did tried SQL Jason's solution but it uses dates in fact table which is not applicable in my case as I have just 1 record for employee, on which i calculate status ("active employee") over multiple months (even years).

I thought there should be a standard technique that i am somehow missing. SQL Jason mentions disconnected date tables but I was not able to create one as CALENDAR function doesn't accept measure as input (i tried to get the date with the help of SELECTEDVALUE) or I can't make it work.

I am sure I am missing something very basic, but can't figure out what is it:(.

[pranit828]

HI @Vladisam 

I'd suggest you to use relative date slicer to select last N months to display corresponding records.

Use a relative date slicer and filter in Power BI Desktop

However, Check the following link about dynamic last n month based on slicer.

Display Last N Months & Selected Month using Single Date Dimension in Power BI