Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I wish to filter Lease end date column by today's date which is available in table in query editor
Please help
= Table.SelectRows(#"Changed Type1", each [date] = Date.From(DateTime.LocalNow()))
You don't need to create a column.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
Add this in the Advanced Editor.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Table.SelectRows equals filter
#"Changed Type1", is my previous step
each [date] date is my column name
Date.From(DateTime.LocalNow())) gets today's date, and formats it as a date
Proud to be a Super User!
Trade you for a kudo or two
Above is the PBIX
Proud to be a Super User!
it's filter by today, i wish to display upcoming lease end date as weel in the table
Hello-
1) Create a custom column. Today's date =
DateTime.LocalNow()
2) Create a conditional column where Is today's date = Yes if today's date is equal to your date column and no if it isn't.
Jared
Thank you!
But actually i wish to filter Lease End date column where only filter dates which is upcoming not past date
See below snap, i wish to filter second column by first column.
I have already created column the way you suggested.
hope i clear in my results
please guide
hi, @Amardeep100115
If Today's Date2 will be updated every day?
If so, create a conditional custom column that if Lease End date column is after Today's Date2, then 1 else 2.
Then you could filter this custom column which is 1.
Or use this formula to create a custom column
if [#"Lease End date -Copy"]>=Date.From(DateTime.LocalNow()) then 1 else 2
Then filter it by the same logic.
here is sample pbix file, please try it.
Regards,
Lin
i got the nearly answer of my query but when i tried to put condiationl formatting it's showing data from all quarters since 2019 to next expiring dates, please see the snap
hi, @Amardeep100115
Could you please share the sample pbix file for us have a test? For it's showing data from all quarters since 2019, there should be something wrong in other.
Regards,
Lin
hi, @Amardeep100115
I am sorry that I did not notice this reply yesterday
Please upload again.
Regards,
Lin
Please find the sample data and result snap
red boxed details should be not displayed and green box details (upcoming lease expiry data) should be display
hope i clear on my results
Did you get my request answer?
hi, @Amardeep100115
Best my test on your sample pbix file, The reason should be these two points:
1. you may try to use this formula to create a flag measure
flag = IF(DATEDIFF(TODAY(),SELECTEDVALUE('data'[Lease End Date - Copy]),DAY)>=0,DATEDIFF(TODAY(),SELECTEDVALUE('data'[Lease End Date - Copy]),DAY))
2. For your dataset, I found that in your formula, you used [Lease End Date - Copy] column, but in the visual, you used [LED] as Year and [FiscQtr] as Quarter. But [FiscQtr] is not defined by [Lease End Date - Copy].
If you should add a quarter column based on [Lease End Date - Copy], then use it in the visual.
New FiscYr = "Q"&FORMAT(data[Lease End Date - Copy],"Q")
Result:
and here is sample data, please try it.
Best Regards,
Lin
One thing which should be there, when i select month then the table should be replect data that of from the date 14 from that month
hope i am crear on this, you are very near to my results
hi, @Amardeep100115
"when i select month then the table should be replect data that of from the date 14 from that month"
what is the month do you select, and what is the date 14?
could you explain the output with the sample pbix file?
Best Regards,
Lin
Sorry! my Bad,
I mean i have data for last 12 months and i have merged them, i wish to filter visual according to month selection.
14 is the date of the each month or you can assume the 1st date of the each month for filter that month's data with upcoming lease expiring details, like you did this month's date "10/14/2019". but i am looking for results according to each month's 1st date.
or
do i need to add column date.firstdayofthe month instade of column "Today". and it should be in query editor.
What you think.
Hope i am clear on my query.
hi, @Amardeep100115
It's not sure that I understood it correctly, do you want a date for slicer, then choose on date and use it as "Today" date in the measure?
If so, you could just use 'Fiscal Calendar'[Date] as a slicer and adjust the formula as below:
Measure flag = IF(DATEDIFF(SELECTEDVALUE('Fiscal Calendar'[Date],TODAY()),SELECTEDVALUE('data'[Lease End Date - Copy]),DAY)>=0,DATEDIFF(SELECTEDVALUE('Fiscal Calendar'[Date],TODAY()),SELECTEDVALUE('data'[Lease End Date - Copy]),DAY))
note: 'Fiscal Calendar'[Date] must be a separate date table, if you need Fiscal Calendar create a relationship with data table, you need to change it with a separate date table.
Best Regards,
Lin
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |