Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello Power BI Community,
I'm currently working on building a trend chart in Power BI that displays billed_users_data for the last six months. I'm seeking guidance on creating a trend chart with specific criteria:
- The chart should display data for the last six months only.
- The six-month duration should align with the month selected in the month parameter. For example, if the month selected is December, then the trend should include December, November, October, September, August, and July.
- The trend chart should remain static, reflecting the same six-month period regardless of date changes.
- I aim to visualize trends over this fixed time frame using a line chart or preferred visualization method.
I'm currently working on converting Tableau reports to Power BI, and these are the tableau calculations i used to achieve my desired output.
1. Filter: 6 Selected Months = DATEDIFF('month',[Date Truncated to Month],[Selected Month]) < 6
AND
DATEDIFF('month',[Date Truncated to Month],[Selected Month]) >= 0
2. Date Truncated to Month = DATETRUNC('month',[Report Month])
3. Selected Month = DATE(str([YEAR]) +"-"+ STR([MONTH]) +"-01")
4. Filter: Month and Year = DATE(STR([YEAR]) + "-" + STR([MONTH]) + "-01")
= [Date for Filter]
5. Date for Filter = DATE(DATETRUNC('month', [Report Month]))
Could someone please provide guidance or steps on how to replicate this functionality in Power BI? I'm specifically looking to create a static trend chart for the last six months, aligned with the selected month parameter, similar to the approach in Tableau.
Thank you in advance for your assistance!
https://drive.google.com/file/d/1fzfck3WHfn7xLh0OrHTEXg0wpaiDs94S/view?usp=sharing
Best regards,
Manoj Prabhakar
Solved! Go to Solution.
Hi @manoj_0911 ,
According to your description, here are my steps you can follow as a solution.
(1) This is my test data.
(2) We can create a slicer table and a measure. Note that this slicer table has no model relationship with the source table.
SlicerTable = CALENDAR(MIN('Table'[date]),MAX('Table'[date]))
Flag =
var _year=SELECTEDVALUE('SlicerTable'[Date].[Year])
var _month=SELECTEDVALUE(SlicerTable[Date].[MonthNo])
var _date1=EOMONTH(DATE(_year,_month,1),-6)+1
var _date2=EOMONTH(DATE(_year,_month,1),0)
RETURN IF(MAX('Table'[date])<=_date2 && MAX('Table'[date]) >= _date1,1,0)
(3) Place [Flag=1] on the visual object screening and then the result is as follows.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @manoj_0911 ,
According to your description, here are my steps you can follow as a solution.
(1) This is my test data.
(2) We can create a slicer table and a measure. Note that this slicer table has no model relationship with the source table.
SlicerTable = CALENDAR(MIN('Table'[date]),MAX('Table'[date]))
Flag =
var _year=SELECTEDVALUE('SlicerTable'[Date].[Year])
var _month=SELECTEDVALUE(SlicerTable[Date].[MonthNo])
var _date1=EOMONTH(DATE(_year,_month,1),-6)+1
var _date2=EOMONTH(DATE(_year,_month,1),0)
RETURN IF(MAX('Table'[date])<=_date2 && MAX('Table'[date]) >= _date1,1,0)
(3) Place [Flag=1] on the visual object screening and then the result is as follows.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1. Filter: 6 Selected Months = DATEDIFF('month',[Date Truncated to Month],[Selected Month]) < 6
AND
DATEDIFF('month',[Date Truncated to Month],[Selected Month]) >= 0
If date is selected and then you need 6 months, you need a disconnected table, if the date is not filtered then you can simply filter
//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(sales[Gross Sales]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))
//Last 12 based on today
new measure =
var _max = eomonth(today(),0)
var _min = eomonth(today(),-12)+1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))
2. Date Truncated to Month = DATETRUNC('month',[Report Month])
Month Start Date = eomonth([Date], -1) +1
3. Selected Month = DATE(str([YEAR]) +"-"+ STR([MONTH]) +"-01")
selectedvalue(Date[Year]) & "-" &selectedvalue(Date[Month]) & "-01"
4. Filter: Month and Year = DATE(STR([YEAR]) + "-" + STR([MONTH]) + "-01")
selectedvalue(Date[Year]) & "-" &selectedvalue(Date[Month]) & "-01"
= [Date for Filter]
5. Date for Filter = DATE(DATETRUNC('month', [Report Month]))
Hi @manoj_0911
this is somwhat similar to this problem statment you can check in this link: https://community.fabric.microsoft.com/t5/Desktop/Dynamic-X-axis-based-on-date-selection-in-slicer/m...
Regards
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
108 | |
101 | |
38 | |
35 |
User | Count |
---|---|
149 | |
122 | |
76 | |
74 | |
52 |