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 There
I'm trying to get a count of support tickets older than 5 weeks.
The (relevant) table data in question for the visual is; Title (Count), Logged On (Date) (I also have this field available in DateTime).
With the visual level filter I can set this to "Is Before X Date" or "In the last 5 weeks" - but what I need is "Logged On Older than 5 weeks" - as "In the last 5 weeks will be"
I've tried creating a custom column with the below. which I obtained from here
Table.SelectRows(#"Logged On (Date)", each [Date] < Date.AddDays(DateTime.LocalNow(),-35))
However, this returns the new custom column as a series of errors; am I formatting the above incorrectly? Or would this work with the data I have? (Sample Cell: 01/08/2018)
Any help would be greatly appreciated!
Thanks
Andy
Solved! Go to Solution.
Hi @AndyB ,
I could not understand what is your desired result. Which data you want to compare of "Before 5 Weeks Ago"? Today or select a day to be dynamic? If the later, you could refer to below example:
Sample(week is a calculated column):
Create a new table:
New Table = SELECTCOLUMNS('Table1',"Date",[Date],"Week",[Week])
Use the [Date] in new table as slicer and create below measure:
Measure = IF(CALCULATE(MAX('Table1'[Week]))+5<=SELECTEDVALUE('New Table'[Week]),1,0)
Set the measure filter as 1 and you could get below result(based on the slicer you have chosen):
You could also download the pbix file to have a view.
Regards,
Daniel He
Hi @AndyB ,
Could you please post some simple sample data and your desired result to have a test if possible?
You could see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Regards,
Daniel He
Hi Dan
Thanks for that link - I'll be sure to read through it and adhere to the guidelines in future!
Sample Data:
Title | Logged On (DateTime) |
Password Reset | 01/08/2018 11:20 |
RE: Procurement | 01/08/2018 13:21 |
Essentiallly what I want to achieve is a visual with "Count of Title" filtered by "Before 5 Weeks Ago" - but dynamically - so similar to the below, but in the same format as the relative date filtering, where it would be referencing "Before 5 Weeks Ago" as opposed to Before 14/04/2019 for instance.
Is there a way for me to create a custom column somewhere to do this? Or something similar?
Thanks
Andy
Hi @AndyB ,
I could not understand what is your desired result. Which data you want to compare of "Before 5 Weeks Ago"? Today or select a day to be dynamic? If the later, you could refer to below example:
Sample(week is a calculated column):
Create a new table:
New Table = SELECTCOLUMNS('Table1',"Date",[Date],"Week",[Week])
Use the [Date] in new table as slicer and create below measure:
Measure = IF(CALCULATE(MAX('Table1'[Week]))+5<=SELECTEDVALUE('New Table'[Week]),1,0)
Set the measure filter as 1 and you could get below result(based on the slicer you have chosen):
You could also download the pbix file to have a view.
Regards,
Daniel He
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |