Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Everyone,
I would like to create a specified date range depending on user input. This could be done in a slicer. Here's an example. I want to always show the previous 6 months of data based on a date:
IE. User inputs 6/01/2022 and the visualizations would then refresh to show the previous 6 months.
Any help would be greatly appreciated.
Thanks!
Anthony
Solved! Go to Solution.
Hi @anthonydiclemen ,
According to your description, I create a sample.
Here's my solution.
1.Create a new Date table, don't make relationship between the two tables.
Date = VALUES('Table'[Date])
2.Create a measure.
Check =
IF (
ISFILTERED ( 'Date'[Date] ),
IF (
MAX ( 'Table'[Date] )
IN DATESINPERIOD ( 'Date'[Date], SELECTEDVALUE ( 'Date'[Date] ), -6, MONTH ),
1
),
1
)
Put the measure in the visual filter and select its value to 1, then put the new Date column in the slicer, get the result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @anthonydiclemen ,
Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.
Best Regards,
Community Support Team _ kalyj
Hi @anthonydiclemen ,
According to your description, I create a sample.
Here's my solution.
1.Create a new Date table, don't make relationship between the two tables.
Date = VALUES('Table'[Date])
2.Create a measure.
Check =
IF (
ISFILTERED ( 'Date'[Date] ),
IF (
MAX ( 'Table'[Date] )
IN DATESINPERIOD ( 'Date'[Date], SELECTEDVALUE ( 'Date'[Date] ), -6, MONTH ),
1
),
1
)
Put the measure in the visual filter and select its value to 1, then put the new Date column in the slicer, get the result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
You may refer to my solution here - Flex a Pivot Table to show data for x months ended a certain user defined month.
Download the workbook from there and do not open it. Open PowerBI Desktop and import this workbook into that.
Hi,
Thank you. This seems like a great solution for Excel but not exactly sure how I can dynamically use this in Power BI. Any guidance would be appreciated
Hi,
As mentioned in my previous message, open PowerBI Desktop and import the MS Excel workbook. Once imported, please spend some time in studying my solution.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
66 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |