date filtering usint 2 dates in the table

[ebrodie]

I am new to PowerBI and loving it so far

I have a simple task list with start and close dates – at least it seems simple.  I would usually use multiple OR conditions in an MS Access query

I want to filter the records to show all tasks that are open within a date range

if I want to see tasks between 9/1/22 and 9/30/22, I want to see all tasks where:

• Opened anytime and closed within the date range
• Opened before 9/30/22 and still open
• Opened before 9/30/22 and closed after 9/30/22

Task	start date	close date	DISPLAY
1	7/1/22	9/15/22	YES
2	8/1/22		YES
3	8/1/22	10/15/22	YES
4	8/1/22	8/25/22	NO
5	10/15/22		NO

[jbwtp]

Hi @ebrodie,

In Power Query you can use OR/AND in PBI in the same fashion as you would do in MS Access:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLXN9Q3MgIyLPUNTSGsSNdgpVidaCUQ2wImrYAkYYwsYWiAodEEWd5C3wgq7ecPljVF1aQAlYkFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Task " = _t, #"start date" = _t, #"close date" = _t, DISPLAY = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Task ", Int64.Type}, {"start date", type text}, {"close date", type text}, {"DISPLAY", type text}}),
    Main = Table.TransformColumns(#"Changed Type",{{"start date", each Date.From(DateTimeZone.From(_, "en-US")), type date}, {"close date", each Date.From(DateTimeZone.From(_, "en-US")), type date}}),
    OpenedBeforeClosedAfter = Table.SelectRows(Main, each [start date] < #date(2022, 9, 30) and [close date] > #date(2022, 9, 30)),
    OpenedBeforeNotClosed = Table.SelectRows(Main, each [start date] < #date(2022, 9, 30) and [close date] = null),
    ClosedWithinRage = Table.SelectRows(Main, each [close date] >= #date(2022, 9, 1) and [close date] <= #date(2022, 9, 30)),
    CombinedAndOptimised = Table.SelectRows(Main, each ([start date] >= #date(2022, 9, 1) and [close date] <= #date(2022, 9, 30)) or ([start date] < #date(2022, 9, 30) and ([close date] = null or [close date] > #date(2022, 9, 30))))
in
    CombinedAndOptimised

In DAX you replace it with II (for OR) and && (for AND):

Filter('Table', ([close date] > Date(2022,9,1) && [close date] < Date(2022,9,30)) || ([start date] < Date(2022,9,30) && (ISBLANK([close date])  || [close date] > Date(2022,9,30))))

Cheers,
John

[ebrodie]

i think i'm more of a rookie than i thought...

do i add this as a measure?

Measure = Filter('ve2doTaskALL', ([ClosedDate] > Date(2022,9,1) && [closeddate] < Date(2022,9,30)) || ([startdate] < Date(2022,9,30) && (ISBLANK([closeddate]) || [ClosedDate] > Date(2022,9,30))))

i get the error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value"

thanks in advance

[jbwtp · Accepted solution]

This returns a table which you can feed to measure, something like:

Measure = SUMX(Filter('ve2doTaskALL', ([ClosedDate] > Date(2022,9,1) && [closeddate] < Date(2022,9,30)) || ([startdate] < Date(2022,9,30) && (ISBLANK([closeddate]) || [ClosedDate] > Date(2022,9,30)))), [Revenue])

Cheers,

John

[ebrodie]

John

you are the BEST!

is there a way to pass in as parameters the hard coded dates?

[jbwtp]

Yes, tipically for DAX you either create a measure which would get a value from the dates table managed by a slicer, and use this measure instead of the hardcoded date.

[ebrodie]

@jbwtp 

so i am using the between sucessfully.

now i wantto reference a start and end data that is selected from a calendar by the user

can the user select a start date and end date from an "UNBOUND" control that i can pass into the measure for the filtering of the records?

[jbwtp]

Hi @ebrodie,

Do you need any further help with this?

Thanks,

John