Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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 |
Solved! Go to 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
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
i think i'm more of a rookie than i thought...
do i add this as a measure?
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
John
you are the BEST!
is there a way to pass in as parameters the hard coded dates?
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.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.