Forum Discussion
Find Min date by Year (Power Query/M)
I have a calendar table with a date column. I also have another column that represents something like a fiscal year and that column just has the year for each date in it. So if the fiscal year started 1st of August every year, then the fiscal year that started 8/1/2021 would just have "2021" in it from 8/1/2021 until 7/31/2022.
For a different formula, I need to figure out the min date of that fiscal year column for each year dynamically so I can establish a "start date" for a YTD formula.
So in the example above, if Im using todays date of 4/28/2022, the associated fiscal year column would read 2021 and the min date for that would be 8/1/2021. How can I get to that? Or can someone explain how to filter/sort that Date field by Fiscal Year field - that would be awesome! I feel like this should be really easy and I can get the min date in the date column with List.Min but I cannot figure out how to sort/filter by that other column.
If this makes it easier, convert this Dax Column to Power Query:
Reason for converting is I want to use this to help create custom YTD flag columns in my Calendar table for PYTD/YTD measures that start with the Fiscal year instead of calendar year.
Hi Anonymous ,
So simply, the logic would be if Month>=8 then the fiscal year is current year, otherwise previous year, then the fiscal date is always xxxx-August-01, right?
Please try the following to add custom columns:
if Date.Month([Dates])>=8 then Date.Year([Dates]) else Date.Year([Dates])-1Date.From( Number.ToText( [Fiscal Year])&"-08-01")Below is the whole M syntax:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ31zdUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t]), #"Added Custom" = Table.AddColumn(Source, "EndDate", each Date.From(DateTime.LocalNow())), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"StartDate", type date}, {"EndDate", type date}}), #"Added Custom1" = Table.AddColumn(#"Changed Type", "Dates", each {Number.From([StartDate])..Number.From([EndDate])}), #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom1", "Dates"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"StartDate", "EndDate"}), #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Fiscal Year", each if Date.Month([Dates])>=8 then Date.Year([Dates]) else Date.Year([Dates])-1), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Min Fiscal Date", each Date.From( Number.ToText( [Fiscal Year])&"-08-01")) in #"Added Custom3"Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Can you provide some data for this problme to be worked upon?
Refer to this - How to provide sample data in the Power BI Forum - https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Ideal will be to upload the file without confidential/sensitive data to a cloud storage service such as Onedrive/Google Drive/Dropbox/Box (Onedrive preferred) and share the link here.Hi Anonymous ,
So simply, the logic would be if Month>=8 then the fiscal year is current year, otherwise previous year, then the fiscal date is always xxxx-August-01, right?
Please try the following to add custom columns:
if Date.Month([Dates])>=8 then Date.Year([Dates]) else Date.Year([Dates])-1Date.From( Number.ToText( [Fiscal Year])&"-08-01")Below is the whole M syntax:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ31zdUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t]), #"Added Custom" = Table.AddColumn(Source, "EndDate", each Date.From(DateTime.LocalNow())), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"StartDate", type date}, {"EndDate", type date}}), #"Added Custom1" = Table.AddColumn(#"Changed Type", "Dates", each {Number.From([StartDate])..Number.From([EndDate])}), #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom1", "Dates"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"StartDate", "EndDate"}), #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Fiscal Year", each if Date.Month([Dates])>=8 then Date.Year([Dates]) else Date.Year([Dates])-1), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Min Fiscal Date", each Date.From( Number.ToText( [Fiscal Year])&"-08-01")) in #"Added Custom3"Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
