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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Amardeep100115
Post Prodigy
Post Prodigy

Sort column by today's date

Hi 

 

I wish to filter Lease end date column by today's date which is available in table in query editor

 

Please help

 

Sorting.JPG

AB
21 REPLIES 21
Nathaniel_C
Super User
Super User

@Amardeep100115 ,

= 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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Amardeep100115 ,

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

date.PNG

 

 

 

 

 

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

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Amardeep100115 ,

Trade you for a kudo or twoSmiley Wink

 

date 

 

Above is the PBIX





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




it's filter by today, i wish to display upcoming lease end date as weel in the table

 

 

AB

Can I have a working file for my reference
AB
Anonymous
Not applicable

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

 

Sorting.JPG

AB

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.

 

4.JPG

5.JPG

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-lili6-msft 

 

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

 

 

 Conditional format.JPG

AB

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-lili6-msft 

 

Did you got the file 

have you understood what i am looking for?


AB

hi, @Amardeep100115 

I am sorry that I did not notice this reply yesterday

This item might not exist or is no longer available

Please upload again.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

Click here 

 

Conditional Formatting.JPG

AB

Did you get my request answer?

AB

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:

8.JPG

 

and here is sample data, please try it.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

AB

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

AB

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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