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.
Hullo,
I have an Azure dataset that pulls data from the previous calendar month, and current incomplete month. So as today is January 19th 2022 - the dataset holds December 2021 and the first half of January 2022. Next month, it will drop December 2021 and so forth.
I also have a data pull of historic Azure data which holds 12 calendar months. But, I need to EXCLUDE the previous calendar month so I don't duplicate volumes. I can't manually exclude December 2021, because next month I will need to exclude January 2022 and so forth.
Please help!
Solved! Go to Solution.
Hi @Anonymous ,
Add a custom column:
column = if Date.StartOfMonth([date]) > Date.From(Date.StartOfMonth(Date.AddMonths(DateTime.LocalNow(), -1))) then 1 else 0
Then filter the table by custom column.
Best Regards,
Jay
Hi @Anonymous ,
Add a custom column:
column = if Date.StartOfMonth([date]) > Date.From(Date.StartOfMonth(Date.AddMonths(DateTime.LocalNow(), -1))) then 1 else 0
Then filter the table by custom column.
Best Regards,
Jay
@Anonymous , Try to add filter like
= Table.SelectRows(#"Changed Type", each [Column1] < Date.StartOfMonth( Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-1)))
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjbQ9csv0zUyMDJUitUB8g11XVKTEXxDfUN9IMdIKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Column1] < Date.StartOfMonth( Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-1)))
in
#"Filtered Rows"
example code
Add new column with this formula
https://docs.microsoft.com/en-us/powerquery-m/date-isinpreviousmonth
Then if is previous month then filter it
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 |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |