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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
johncassidy
Frequent Visitor

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.

3 ACCEPTED SOLUTIONS
Mariusz
Community Champion
Community Champion

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.

View solution in original post

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

View solution in original post

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.

View solution in original post

4 REPLIES 4
Mariusz
Community Champion
Community Champion

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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