Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Vladisam
Helper II
Helper II

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

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

2 REPLIES 2
Vladisam
Helper II
Helper II

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
Community Champion
Community Champion

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





PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.