Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
manoj_0911
Advocate II
Advocate II

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

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

1 ACCEPTED SOLUTION
v-tangjie-msft
Community Support
Community Support

Hi @manoj_0911 ,

 

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

vtangjiemsft_0-1708672203314.png

(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.

vtangjiemsft_1-1708672433689.png

 

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. 

View solution in original post

3 REPLIES 3
v-tangjie-msft
Community Support
Community Support

Hi @manoj_0911 ,

 

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

vtangjiemsft_0-1708672203314.png

(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.

vtangjiemsft_1-1708672433689.png

 

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
Super User
Super User

@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
Super User
Super User

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.