Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
70 |
User | Count |
---|---|
114 | |
100 | |
97 | |
72 | |
71 |