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
mirfanshahid
New Member

Calendar date function

Hi All, 

I have two different columns 1 is date column and 2. Status column. 
I want to select a date from column 1 ( 1-June-2020 to Today)
From the Status column, I want to filter only Validated status how can I do it

1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

Hello @mirfanshahid 

 

you meand to add a filter that filters a daterange and a Status? If so, see this code how to use Table.SelectRows for that

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjAyUdpbDEnMyUxJLUFKVYHYiEMUTCL79EoQxF0shUz8AImy5jA3y6gJLm2HQZAo2zwCZhhDAOw3WGhljtiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date, "de-DE"}, {"Status", type text}}),
    SelectDateAndStatus = Table.SelectRows
    (
        #"Changed Type",
        each [Date]>=#date(2020,06,01) and [Date]<=Date.From(DateTime.FixedLocalNow()) and [Status]="Validated"
    )
in
    SelectDateAndStatus

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

2 REPLIES 2
Jimmy801
Community Champion
Community Champion

Hello @mirfanshahid 

 

you meand to add a filter that filters a daterange and a Status? If so, see this code how to use Table.SelectRows for that

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjAyUdpbDEnMyUxJLUFKVYHYiEMUTCL79EoQxF0shUz8AImy5jA3y6gJLm2HQZAo2zwCZhhDAOw3WGhljtiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date, "de-DE"}, {"Status", type text}}),
    SelectDateAndStatus = Table.SelectRows
    (
        #"Changed Type",
        each [Date]>=#date(2020,06,01) and [Date]<=Date.From(DateTime.FixedLocalNow()) and [Status]="Validated"
    )
in
    SelectDateAndStatus

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Greg_Deckler
Super User
Super User

@mirfanshahid - Sorry, I'm not following, sample data and expected output please.

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please 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

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

Top Solution Authors
Top Kudoed Authors