Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have Table1[dateExam] and Table1[numberPeople].
First, I need to use a CALENDAR (range slicer) to select a date (it must only consider the max date of range slicer selected). For example, I can select 10/01/2021 as my maximum date.
Second, I need to check one of the options: (in Segment). For example, I can check the option “last 7 days”.
Then, it would return me the numberPeople for the “last 7 days” of the selected date in the CALENDAR.(So the “last 7 days” is base on the CALENDAR (range slicer) I have selected.)
In other words, it would return me in a graphic, the numberPeople between 03/01/2021 and 10/01/2021.
For this example, in a graphic, I would have for each day between 03/01/2021 and 10/01/2021, the numberPeople that have done dateExam from Table1[dateExam].
Thanks!
Solved! Go to Solution.
Hi @game1 ,
You can try formula like below:
NumberPeopleLastDays_slicer =
VAR MaxSelectedDate =
MAX ( 'Calendar'[Date] )
VAR MinSelectedDate =
MaxSelectedDate
- CALCULATE (
MAX ( slicer[Value] ),
FILTER ( slicer, slicer[last_] = SELECTEDVALUE ( slicer[last_] ) )
)
RETURN
CALCULATE (
SUM ( Table1[numberPeople] ),
FILTER (
Table1,
Table1[dateExam] >= MinSelectedDate
&& Table1[dateExam] <= MaxSelectedDate
)
)
create slicer table and calendar table:
Best Regards,
Adamk Kong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @game1 ,
You can try formula like below:
NumberPeopleLastDays_slicer =
VAR MaxSelectedDate =
MAX ( 'Calendar'[Date] )
VAR MinSelectedDate =
MaxSelectedDate
- CALCULATE (
MAX ( slicer[Value] ),
FILTER ( slicer, slicer[last_] = SELECTEDVALUE ( slicer[last_] ) )
)
RETURN
CALCULATE (
SUM ( Table1[numberPeople] ),
FILTER (
Table1,
Table1[dateExam] >= MinSelectedDate
&& Table1[dateExam] <= MaxSelectedDate
)
)
create slicer table and calendar table:
Best Regards,
Adamk Kong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@game1
To calculate for any time period based on the current date selection, follow the following pattern. you can chagne the Day to Month, Quarter ans so on,
Last 7 Days = CALCULATE( [Sales Amount] , DATESINPERIOD( 'Date'[Date] , MAX( 'Date'[Date] ) , -7 , DAY ) )
. In this example, the sales amount is calculated for the past 7 days from the current date selection. It takes the max of the current range.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
No! It is not working. I can select 2 options last 7 days or last 60 days and I need also the calendar (range slicer).
@game1
Sharing a dummy Power BI file representing your scenario would be beneficial. You can save the Power BI file on Google Drive or any other cloud storage platform and provide the link here. Kindly ensure that permission is granted to open the file.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group