Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I am using Power Query, and I have 2 tables that I am working with. I need to check if the route date (Table 1) exist in between 2 dates (from Table 2) and pull in the FY (Table 1).
Table 1
Route Date FY
10/1/2019
6/17/2020
5/2/2021
Table 2
Start End FY
10/1/2019 9/30/2020 FY20
10/1/2020 9/30/2021 FY21
10/1/2021 9/30/2022 FY22
Solved! Go to Solution.
Not necessary to quote any other calendar table since the FY end date ("9/30") is fixed all the time. One step is enough,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQN9Q3MjC0VIrViVYy0zc0B/KMDMA8U30jEMdQKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Route Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Route Date", type date}}),
FY = Table.AddColumn(#"Changed Type", "FY", each if Date.Month([Route Date]) > 9 then "FY" & Date.ToText(Date.EndOfYear([Route Date])+#duration(1,0,0,0), "yy") else "FY" & Date.ToText([Route Date], "yy"))
in
FY
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Not necessary to quote any other calendar table since the FY end date ("9/30") is fixed all the time. One step is enough,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQN9Q3MjC0VIrViVYy0zc0B/KMDMA8U30jEMdQKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Route Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Route Date", type date}}),
FY = Table.AddColumn(#"Changed Type", "FY", each if Date.Month([Route Date]) > 9 then "FY" & Date.ToText(Date.EndOfYear([Route Date])+#duration(1,0,0,0), "yy") else "FY" & Date.ToText([Route Date], "yy"))
in
FY
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Thank you!!
Here is an alternative way by using Table.SelectRows - Solution file uploadd to -
https://1drv.ms/x/s!Akd5y6ruJhvhuR_VfVLkonWse4WC?e=VrKpVz
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Route Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(Table2, (x)=> x[End]>=[Route Date] and x[Start]<=[Route Date]){0}[FY])
in
#"Added Custom"
Here's one way:
let
//Read in the dates table
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
dateTable = Table.TransformColumnTypes(Source,{{"Route Date", type date}}),
//Read in the FY table
Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
fyTable=Table.TransformColumnTypes(Source2,{
{"Start", type date},{"End", type date},{"FY", type text}
}),
//Add a column to the FY table that includes all of the dates
#"Added Custom" = Table.AddColumn(fyTable, "Custom", each List.Dates([Start], Duration.Days([End]-[Start])+1,#duration(1,0,0,0))),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
//Join the two tables (JoinKind.LeftOuter) and return the FY
joined = Table.Join(dateTable,"Route Date",#"Expanded Custom","Custom",JoinKind.LeftOuter),
//Remove unneeded columns
#"Removed Columns" = Table.RemoveColumns(joined,{"Start", "End", "Custom"})
in
#"Removed Columns"
Check out the July 2025 Power BI update to learn about new features.