Forum Discussion
dynamic line chart
i have a line chart showing monthly sales . if i click on any month in the line chart , it should display the sales for that selected month along with the previous two months (same as line chart)
also, if i unselect the month (i.e click outside the line chart or any other area in canvas), the visual should revert back to showing sales for all months.
13 Replies
Hi powerbiexpert22 ,
You can achieve this behavior using a combination of DAX and a disconnected Date table.Here’s the general idea:
- Create a disconnected Date table (if you don’t already have one).
- Use a measure that checks if a month is selected, and if so, filters the data to include that month and the two months before it.
Example DAX:
Sales (Selected + Prev 2 Months) = VAR SelectedMonth = SELECTEDVALUE('Date'[MonthYear]) -- assuming MonthYear is like "2024-03" VAR SelectedDate = CALCULATE(MAX('Date'[Date]), 'Date'[MonthYear] = SelectedMonth) VAR StartDate = EDATE(SelectedDate, -2) RETURN CALCULATE( [Total Sales], FILTER( ALL('Date'), 'Date'[Date] >= StartDate && 'Date'[Date] <= SelectedDate ) )This way:
- When a month is selected, it shows that month + 2 previous months.
- When nothing is selected, it shows all months (default behavior of the visual).
Let me know if you need help adapting this to your model.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AIHi burakkaragoz ,
I have tried your approach as well however nothing is happening once i click on any data point on visual
please see below pbix
https://drive.google.com/file/d/1NR3WBeQW8dwXOdCN3opuzF6-jhVlg6XQ/view?usp=drive_link
Please follow the below steps:
1. Create a Date table with a column: Date[MonthYear] (formatted like "YYYY-MM" or a proper date)2. Create a measure to control the dynamic range
Sales (Selected + Prev 2 Months) =
VAR SelectedMonth =
SELECTEDVALUE('Date'[MonthYear]) -- e.g., "2024-03"
VAR SelectedDate =
CALCULATE(
MAX('Date'[Date]),
'Date'[MonthYear] = SelectedMonth
)
VAR StartDate =
EDATE(SelectedDate, -2)
VAR IsMonthSelected =
NOT ISBLANK(SelectedMonth)
RETURN
IF(
IsMonthSelected,
CALCULATE(
[Total Sales],
FILTER(
ALL('Date'),
'Date'[Date] >= StartDate &&
'Date'[Date] <= SelectedDate
)
),
[Total Sales] -- fallback: show all months
)
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!Hi grazitti_sapna ,
I have tried your approach however nothing is happening once i click on any data point on visual
please see below pbix
https://drive.google.com/file/d/11J2eg84_GyyPVD0Thtn6wRHtO0JrPwrS/view?usp=drive_link
Hi burakkaragoz , grazitti_sapna
i would like to acheive below functionality as an example
Line Chart showing Sales by Year Month:
Let say I selected 2021-09 then below should be the output
Let say if select outside the line then chart should reset to show all sales
Hi powerbiexpert22 , Thank you for reaching out to the Microsoft Community Forum.
In Power BI, clicking a data point on a line chart just highlights it without filtering the data model, so our DAX logic for showing the selected month and the two previous ones doesn’t work. Use a slicer tied to a Month Selector table. This way, you can pick a month and the chart will automatically show that month plus the two before it. If you clear the slicer, the chart goes back to showing all months. Or you could use a table or matrix visual with the same month selector as a clickable filter to get the same result.
Please refer attached .pbix file for reference and share your thoughts.
If this helped solve the issue, please consider marking it “Accept as Solution” and giving a ‘Kudos’ so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
Hi powerbiexpert22 ,
I wanted to follow up and see if you’ve had a chance to review the information provided here.
If any of the responses helped solve your issue, please consider marking it "Accept as Solution" and giving it a 'Kudos' to help others easily find it.
Let me know if you have any further questions!Hi powerbiexpert22 ,
I hope the information shared was helpful. If you have any additional questions or would like to explore the topic further, feel free to reach out. If any of the responses resolved your issue, please mark it "Accept as solution" and give it a 'Kudos' to support other members in the community.
Thank you!Hi powerbiexpert22 , I hope you're doing well! Just checking in to see if you had a chance to review the details shared earlier. If any of the information addressed your needs, feel free to mark it as "Accept as Solution" or give it a 'Kudos' to help others in the community. Please let me know if you have any further questions!
Hi powerbiexpert22 , hope you are doing well. I just wanted to confirm if you checked page 2 of the .pbix file.
From my understanding, it appears to be working fine as per your requirement. If you are still not satisfied after checking both pages, please describe how it doesn't meet your expectations and what you would like in the final output. Additionally, let us know if you have any specific process in mind that you want us to follow.
Thank you.
