Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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:
Vlad
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:(.
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
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 |
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |