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.
Hi,
I want to generate a list of every days since the 1st of april to the 31th of march, according to the current year. There's a certain level of difficulty, linked to the fact that there's two years involved in the financial year).
Ex :
Thanks!
Solved! Go to Solution.
a little change 🙂
let
StartDate = if #date(Date.Year(DateTime.LocalNow()), 4, 1) > DateTime.Date(DateTime.LocalNow()) then #date(Date.Year(DateTime.LocalNow())-1, 4, 1) else #date(Date.Year(DateTime.LocalNow()), 4, 1),
EndDate = DateTime.LocalNow(),
DateList = List.Dates(StartDate, Number.From(EndDate)- Number.From(StartDate)+1 ,#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Named as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Named as Date",{{"Date", type date}})
in
#"Changed Type"
do not hesitate to give a kudo to useful posts and mark solutions as solution
Hi @NumeroENAP
if its5 of March 2020?
anyway try DAX table like this
CalendarTable = calendar(date(year(today());4;1);today())
do not hesitate to give a kudo to useful posts and mark solutions as solution
Thanks for the answer. The only thing is that I need it in M Language, because I'm power querying in Excel.
And yes, I also need it to include the date from the part of the year before, if I'm in 2020 (by exemple).
You would be awesome if you could translate it in M Language.
try this technique
let
StartDate= #date(2019,4,1),
EndDate = DateTime.LocalNow(),
DateList = List.Dates(StartDate, Number.From(EndDate)- Number.From(StartDate)+1 ,#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Named as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Named as Date",{{"Date", type date}})
in
#"Changed Type"
do not hesitate to give a kudo to useful posts and mark solutions as solution
It's really nice! The only thing is that I need it to start from scratch when we'll change from financial year 2019-2020 to financial year 2020-2021, by exemple.
Thanks
Yes, indeed hehe. I tried to find the error, but I couldn't solve it myself. 😞
Expression.Error : Sorry... We couldn't apply the operator < to types DateTime and Date.
Details :
Operator=<
Left=2019-12-12 10:30:52
Right=2019-04-01
let
StartDate = if #date(Date.Year(DateTime.LocalNow()), 4, 1) > DateTime.LocalNow() then #date(Date.Year(DateTime.LocalNow())-1, 4, 1) else #date(Date.Year(DateTime.LocalNow()), 4, 1),
EndDate = DateTime.LocalNow(),
DateList = List.Dates(StartDate, Number.From(EndDate)- Number.From(StartDate)+1 ,#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Named as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Named as Date",{{"Date", type date}})
in
#"Changed Type"
a little change 🙂
let
StartDate = if #date(Date.Year(DateTime.LocalNow()), 4, 1) > DateTime.Date(DateTime.LocalNow()) then #date(Date.Year(DateTime.LocalNow())-1, 4, 1) else #date(Date.Year(DateTime.LocalNow()), 4, 1),
EndDate = DateTime.LocalNow(),
DateList = List.Dates(StartDate, Number.From(EndDate)- Number.From(StartDate)+1 ,#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Named as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Named as Date",{{"Date", type date}})
in
#"Changed Type"
do not hesitate to give a kudo to useful posts and mark solutions as solution
Thank you for your patience. You're an angel!
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.