Quarter Index

[johncassidy]

Hi all,

Hopefully someone can help me with something that's wrecking my head - an incrementing quarter index.

I have your standard date table which I think I got off this forum;

myDates =
  GENERATE (
    CALENDAR( 
        DATE(2010,01,01),
        TODAY()),
    VAR startOfWeek = 1 // Where 1 is Sunday and 7 is Saturday, thus a 3 would be Tuesday    
    VAR currentDay = [Date]
    VAR days = DAY( currentDay )
    VAR months = MONTH ( currentDay )
    VAR years = YEAR ( currentDay )
    VAR nowYear = YEAR( TODAY() )
    VAR nowMonth = MONTH( TODAY() )
    VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1
    VAR todayNum = WEEKDAY( TODAY() )
    VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) )
    VAR nowQuarter = Int(roundup(month([Date])/3,0))
    VAR quarterIndex = Int(roundup(month([Date])/3,0))
  RETURN ROW (
    "day", days,
    "month", months,
    "year", years,
    "dayIndex", dayIndex,
    "weekIndex", weekIndex,
    "monthIndex", INT( (years - nowYear ) * 12 + months - nowMonth ),
    "quarterIndex",Int(roundup(month([Date])/3,0)),
    "quarterIncrementalIndex",Int(ROUNDUP(DATEDIFF([Date], today(),MONTH)/3,0) ),  -- Does not work
    "yearquarterIndex",Concatenate(years,CONCATENATE(" Q",Int(ROUNDUP(month([Date])/3,0)))),
    "yearIndex", INT( years - nowYear )
  )
)

My difficulty is the quarterIncrementalIndex, which is supposed to simply number past quarters from zero (current quarter) to [n].

e.g.

2019 Q2 - 0

2019 Q1 - 1

2018Q4 - 2

2018Q3 - 3

2018Q2 - 4

etc. etc.

the code I currently have is just a function of the current date, rather than the current Quarter. I've tried myriad ideas but nothing works.  I'm one step away from doing this in Excel and importing the table! 🙂

I don't mind if the index increments or decrements - I just want to count quarters!!!

Any suggestions welcome,

thanks

John.

[Mariusz · Accepted solution]

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.

[johncassidy · Accepted solution]

@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.

[Mariusz · Accepted solution]

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.

[johncassidy · Accepted solution]

@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.

[Mariusz · Accepted solution]

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.

[johncassidy]

@Mariusz thank you for that, it introduced a new thing to me insofar as I haven't used Power Query before.  Your snippet was very useful and with the following changes, appears to do exactly what I need;

let
    nowDate = DateTime.Date(DateTime.LocalNow()),
    nowYear = Date.Year(nowDate),
    calendarEndDate = #date(nowYear, 12, 31),
    calendarStartDate = #date(nowYear -2, 1, 1),
    nowWeek = Date.WeekOfYear(nowDate),
    nowMonth = Date.Month(nowDate),
    nowQuarter = Date.QuarterOfYear(nowDate),
    #"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),
    #"Sorted Rows" = Table.Sort(#"Added YearQuarters",{{"Year Quarter", Order.Descending}, {"Date", Order.Descending}})
in
    #"Sorted Rows"

Power Query looks like something I need to learn!

thanks again.