Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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.
@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.
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |