Frequent Visitor

7 years ago

Solved

Quarter Index

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.

Need Help

Mariusz
7 years ago
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
7 years ago
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

7 years ago

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.

4 Replies

Mariusz

Community Champion

7 years ago

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

Frequent Visitor

7 years ago

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

Community Champion

7 years ago

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.

Forum Discussion

Quarter Index

4 Replies

Related content

Multiple conditions Index

Index

Quarter over Quarter calculation - Display Quarters missing

Calculating Quarter over Quarter Change

Fixed Index Column