List of dates since 1st of an exact month (renewable according to the year)

[NumeroENAP]

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 : 

• If it's the 15th of april of 2019, I want my list to contain 15 dates ;
• If it's the 5th of april of 2020, I want my list to contain only 5 dates.

Thanks!

[az38]

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

LinkedIn

[NumeroENAP]

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. 

[az38]

@NumeroENAP 

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

LinkedIn

[NumeroENAP]

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

[az38]

Hi @NumeroENAP
try
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)

Sorry, typing from phone, code could be with a little errors 🙂

[NumeroENAP]

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"

[az38 · Accepted solution]

@NumeroENAP 

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

[NumeroENAP]

Thank you for your patience. You're an angel!