Assistance Needed: Creating a Static Trend Chart for Last Six Months in Power BI

[manoj_0911]

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

[v-tangjie-msft · Accepted 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. 

[amitchandak]

@manoj_0911 ,

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]))

[qqqqqwwwweeerrr]

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