Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
NumeroENAP
Helper III
Helper III

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

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!

1 ACCEPTED SOLUTION
az38
Community Champion
Community Champion

@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

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

8 REPLIES 8
az38
Community Champion
Community Champion

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

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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
Community Champion
Community Champion

@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

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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
Community Champion
Community Champion

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 🙂

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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
Community Champion
Community Champion

@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

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors