Forum Discussion
date filtering usint 2 dates in the table
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 |
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
7 Replies
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 CombinedAndOptimisedIn 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,
Johni 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 advanceThis 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
