Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Extending Date List Values

I have a master calendar table in a Power BI report that is based on a Power BI-created list of dates.  The problem is the dates do not extend far enough - only until June 30th 2022. I want to add d...
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    4 years ago
    let
    Startdate = #date(2020,1,1),
    Span = 5,
    Dates = Table.FromList(List.Dates(Startdate, Duration.TotalDays(Date.EndOfYear(Date.AddYears(Startdate,Span))-Startdate)+1,#duration(1,0,0,0)), Splitter.SplitByNothing(), {"Date"}),
    FY = Table.AddColumn(Dates, "FY",
        each let
            yr = Number.Mod(Date.Year([Date]),100)
            in if Date.Month([Date])>=6 then "FY" & Text.From(yr) & "-" & Text.From(yr+1) else "FY" & Text.From(yr-1) & "-" & Text.From(yr)
    )
in
    FY
gpiero's avatar
gpiero
Skilled Sharer
4 years ago

Anonymous 

here a solution I found helpful. It is verly likely there will be solution more efficient than mine.

In any case  you can set date by Manage Parameters.

/* StartingYear come from a parameter with data input = "mm/dd/yyyy*/
/* StartingYear should always start from January 1t */
StartDate = Date.From(StartingYear),
/* EndingYear come from a parameter with data input = "mm/dd/yyyy */
/* EndigYear should always ending at December, 31st */
EndDate = Date.From(EndingYear),
/*----------------------------------------------------------------------------------------------------------------------------*/
Regards