Quarter Index

Hi johncassidy,

I've got a query editor version of that, please see my calendar.pbix file.

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Mariusz  I am precluded by security restrictions from downloading pbix or indeed anything else from cloud services - if you have a code snippet?

thanks for your help

John C.

Hi johncassidy,

Please see the below

// nowDateTime
let
    Source = DateTime.LocalNow()
in
    Source

// nowDate
let
    Source = Date.From(nowDateTime)
in
    Source

// nowYear
let
    Source = Date.Year(nowDate)
in
    Source

// calendarEndDate
let
    yearCalendarEnds = #date(nowYear, 12, 31)
in
    yearCalendarEnds

// calendarStartDate
let
    yearCalendarEnds = #date(nowYear -2, 1, 1)
in
    yearCalendarEnds

// Calendar
let
    #"Duration In Days" = Number.From(calendarEndDate - calendarStartDate) + 1,
    #"List Dates" = List.Dates(calendarStartDate, #"Duration In Days", #duration(1, 0, 0, 0)),
    #"Table from List Dates" = #table(type table [#"Date"=date], List.Transform(#"List Dates", each {_})),
    #"Added sDate" = Table.AddColumn(#"Table from List Dates", "sDate", each Number.From([Date] - nowDate), Int64.Type),
    #"Added Week" = Table.AddColumn(#"Added sDate", "Week", each " W" & Text.End(Number.ToText(100 + Date.WeekOfYear([Date])), 2), type text),
    #"Added sWeek" = Table.AddColumn(#"Added Week", "sWeek", each Date.WeekOfYear([Date]) - nowWeek, Int64.Type),
    #"Added Month" = Table.AddColumn(#"Added sWeek", "Month", each Text.Start(Date.MonthName([Date]), 3), type text),
    #"Added sMonth" = Table.AddColumn(#"Added Month", "sMonth", each Date.Month([Date]) - nowMonth, Int64.Type),
    #"Added Quarter" = Table.AddColumn(#"Added sMonth", "Quarter", each "Q" & Number.ToText(Date.QuarterOfYear([Date])), type text),
    #"Added sQuarter" = Table.AddColumn(#"Added Quarter", "sQuarter", each Date.QuarterOfYear([Date]) - nowQuarter, Int64.Type),
    #"Added Year" = Table.AddColumn(#"Added sQuarter", "Year", each Number.ToText(Date.Year([Date])), type text),
    #"Added sYear" = Table.AddColumn(#"Added Year", "sYear", each Date.Year([Date]) - nowYear, Int64.Type),
    #"Added Year Week" = Table.AddColumn(#"Added sYear", "Year Week", each [Year] & " " & [Week], type text),
    #"Added sYearWeek" = Table.AddColumn(#"Added Year Week", "sYearWeek", each ( [sYear] * 53 ) + [sWeek], Int64.Type),
    #"Added Year Month" = Table.AddColumn(#"Added sYearWeek", "Year Month", each [Year] & " " & [Month], type text),
    #"Added sYearMonth" = Table.AddColumn(#"Added Year Month", "sYearMonth", each ( [sYear] * 12 ) + [sMonth], Int64.Type),
    #"Added Year Quarter" = Table.AddColumn(#"Added sYearMonth", "Year Quarter", each [Year] & " " & [Quarter], type text),
    #"Added YearQuarters" = Table.AddColumn(#"Added Year Quarter", "sYearQuarter", each ( [sYear] * 4 ) + [sQuarter], Int64.Type)
in
    #"Added YearQuarters"

// nowWeek
let
    Source = Date.WeekOfYear(nowDate)
in
    Source

// nowMonth
let
    Source = Date.Month(nowDate)
in
    Source

// nowQuarter
let
    Source = Date.QuarterOfYear(nowDate)
in
    Source

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.