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.
In power query, I want to limit the amount of data loaded into the model by only including new data within the last 3 months from a dataflow. So far found this YouTube video “ https://youtu.be/zr52Q00SrNM?t=137 ” which almost works
Q. How can I amend the formula below to return exactly 3 months from the current date?
- Is there a different approach that you would suggest in power query to reduce how much data is loaded?
Example:
Power Query settings I used:
Query 01 :
let
Date = {Number.From(#date(2021,1,1))..Number.From(#date(2021,11,18))},
#"Converted to Table" = Table.FromList(Date, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "dates"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"dates", type date}})
in
#"Changed Type"
Query 02 :
let
Source = Dates,
#"Filtered Rows" = Table.SelectRows(Source, each Date.IsInPreviousNMonths([dates], 3) or Date.IsInCurrentMonth([dates]))
in
#"Filtered Rows"
Thanks you
Solved! Go to Solution.
Based on your initial example code:
let
Source = Dates,
#"Filtered Rows" = Table.SelectRows(Source, each [dates] >= Date.AddMonths(Date.From(DateTime.LocalNow()), -3))
in
#"Filtered Rows"
Substitute [dates] here with whatever the date field in your fact table is called.
Pete
Proud to be a Datanaut!
let
Today = Date.From(DateTime.LocalNow()),
#"3m Ago" = Date.AddMonths(Today, -3),
Dates = Table.FromList(
List.Dates(#"3m Ago", Duration.Days(Today - #"3m Ago"), #duration(1,0,0,0)),
Splitter.SplitByNothing(),
{"Date"}
)
in
Dates
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! |
Hi @henrycqc ,
Try this in your filter step:
each [date] >= Date.AddMonths(Date.From(DateTime.LocalNow()), -3)
Pete
Proud to be a Datanaut!
Thanks
HI BA_Pete
How would I use this in a table with multiple columns.
Based on your initial example code:
let
Source = Dates,
#"Filtered Rows" = Table.SelectRows(Source, each [dates] >= Date.AddMonths(Date.From(DateTime.LocalNow()), -3))
in
#"Filtered Rows"
Substitute [dates] here with whatever the date field in your fact table is called.
Pete
Proud to be a Datanaut!
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.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |