cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper II

## Bar Chart First Quarter includes 2022 and 2023

I created a bar chart by quarter. When I clicked the first quarter bar, it includes 1st quarter of 2022 and 2023. How can I just have the first quarter of 2023 and have the bar sorted by Qtr2-22, Q3-22, Q4-22, and Q1-23? Thanks.

1 ACCEPTED SOLUTION
Super User

Hi @danyeungw ,

1. create a year quarter column. ( 2022 Q1 .Q2 , .. etc.)
Example: new column = 'Table'[Year]*100+INT(Left('Table'[Quarter],1)).

Select the required year quarters.

If you want this to be dynamic, consider the concept of "Current Quarter Offset".

Paste the below code into the advanced editor of a blank query:

``````let
/*
****This Calendar was created and provided by Avi Singh****
****This can be freely shared as long as this text comment is retained.****
www.LearnPowerBI.com by Avi Singh
*/
#"LearnPowerBI.com by Avi Singh" = 1,
StartDate = #date(2014, 1, 1),
EndDate = Date.EndOfYear(DateTime.Date(DateTime.FixedLocalNow())) /*was "#date(2017, 1, 1)" Updated on 201802027: hard Coded End of Year caused some formulas to break, switching to dynamic date*/,
//Used for 'Offset' Column calculations, you may Hard code CurrentDate for testing e.g. #date(2017,9,1)
CurrentDate = DateTime.Date(DateTime.FixedLocalNow()),
// Specify the last month in your Fiscal Year, e.g. if June is the last month of your Fiscal Year, specify 6
FiscalYearEndMonth = 6,
#"==SET PARAMETERS ABOVE==" = 1,
#"==Build Date Column==" = #"==SET PARAMETERS ABOVE==",
ListDates = List.Dates(StartDate, Number.From(EndDate - StartDate)+1, #duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(ListDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
// As far as Power BI is concerned, the 'Date' column is all that is needed 🙂 But we will continue and add a few Human-Friendly Columns
#"Changed Type to Date" = Table.TransformColumnTypes(#"Renamed Columns as Date",{{"Date", type date}}),
#"==Add Calendar Columns==" = #"Changed Type to Date",
then [MonthNum] - FiscalYearEndMonth
else [MonthNum] + (12 - FiscalYearEndMonth), type number),
Text.End(
Text.From(
if [MonthNum] > FiscalYearEndMonth
then [Year] + 1
else [Year]
)
, 2
)),

// Can be used to for example to show the past 3 months(CurMonthOffset = 0, -1, -2)
#"Added CurMonthOffset" = Table.AddColumn(#"==Add Calendar Date Offset Columns==", "CurMonthOffset", each ( Date.Year([Date]) - Date.Year(CurrentDate) ) * 12
+ Date.Month([Date]) - Date.Month(CurrentDate), Int64.Type),
// Can be used to for example to show the past 3 quarters (CurQuarterOffset = 0, -1, -2)
( Date.Year([Date]) - Date.Year(CurrentDate) )*4
/*Quarter Difference*/
+ Number.RoundUp(Date.Month([Date]) / 3)
- Number.RoundUp(Date.Month(CurrentDate) / 3),
Int64.Type),
// Can be used to for example to show the past 3 years (CurYearOffset = 0, -1, -2)
// Can be used to for example filter out all future dates
#"Added FutureDate Flag" = Table.AddColumn(#"Added CurYearOffset", "FutureDate", each if [Date] > CurrentDate then "Future" else "Past" ),
// FiscalYearOffset is the only Offset that is different.
// FiscalQuarterOffset = is same as CurQuarterOffset
// FiscalMonthOffset = is same as CurMonthOffset
#"Filtered Rows to CurrentDate" = Table.SelectRows(#"==Add FiscalYearOffset==", each ([Date] = CurrentDate)),
CurrentFiscalYear = #"Filtered Rows to CurrentDate"{0}[FiscalYear],
Number.From(Text.Range(CurrentFiscalYear,2,2))
/*Extract the numerical portion, e.g. FY18 = 18*/),
// Used as 'Sort by Column' for MonthYear columns
#"Added MonthYearNum" = Table.AddColumn(#"==Add General Columns==", "MonthYearNum", each [Year]*100 + [MonthNum] /*e.g. Sep-2016 would become 201609*/, Int64.Type),
then "Weekend"
else "Weekday"),
#"==Improve Ultimate Table" = #"Added WeekdayWeekend",
#"----Add WeekSequenceNum----" = #"==Improve Ultimate Table",
#"Filtered Rows Sundays Only (Start of Week)" = Table.SelectRows(#"----Add WeekSequenceNum----", each ([WeekdayNum] = 0)),
#"Added Index WeekSequenceNum" = Table.AddIndexColumn(#"Filtered Rows Sundays Only (Start of Week)", "WeekSequenceNum", 2, 1),
#"Merged Queries Ultimate Table to WeekSequenceNum" = Table.NestedJoin(#"==Improve Ultimate Table",{"Date"},#"Added Index WeekSequenceNum",{"Date"},"Added Index WeekNum",JoinKind.LeftOuter),
#"Expanded Added Index WeekNum" = Table.ExpandTableColumn(#"Merged Queries Ultimate Table to WeekSequenceNum", "Added Index WeekNum", {"WeekSequenceNum"}, {"WeekSequenceNum"}),
// somehow it ends up being unsorted after Expand Column, should not matter for the end table, but makes it harder to debug and check everything is correct. Thus sorting it.
#"ReSorted Rows by Date" = Table.Sort(#"Expanded Added Index WeekNum",{{"Date", Order.Ascending}}),
#"Filled Down WeekSequenceNum" = Table.FillDown(#"ReSorted Rows by Date",{"WeekSequenceNum"}),
#"Replaced Value WeekSequenceNum null with 1" = Table.ReplaceValue(#"Filled Down WeekSequenceNum",null,1,Replacer.ReplaceValue,{"WeekSequenceNum"}),
#"----WeekSequenceNum Complete----" = #"Replaced Value WeekSequenceNum null with 1",
Current_WeekSequenceNum = #"----WeekSequenceNum Complete----"{[Date = CurrentDate]}?[WeekSequenceNum],
#"Added Custom CurWeekOffset" = Table.AddColumn(#"----WeekSequenceNum Complete----", "CurWeekOffset", each [WeekSequenceNum] - Current_WeekSequenceNum, Int64.Type)
in

create the relationship required.

this date table has a current quarter offset column, set it to less than or equal to -1.

let me know if this resolves the issue.

Appreciate a thumbs up if this resolves the issue.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash
Super User

Hi @danyeungw ,

1. create a year quarter column. ( 2022 Q1 .Q2 , .. etc.)
Example: new column = 'Table'[Year]*100+INT(Left('Table'[Quarter],1)).

Select the required year quarters.

If you want this to be dynamic, consider the concept of "Current Quarter Offset".

Paste the below code into the advanced editor of a blank query:

``````let
/*
****This Calendar was created and provided by Avi Singh****
****This can be freely shared as long as this text comment is retained.****
www.LearnPowerBI.com by Avi Singh
*/
#"LearnPowerBI.com by Avi Singh" = 1,
StartDate = #date(2014, 1, 1),
EndDate = Date.EndOfYear(DateTime.Date(DateTime.FixedLocalNow())) /*was "#date(2017, 1, 1)" Updated on 201802027: hard Coded End of Year caused some formulas to break, switching to dynamic date*/,
//Used for 'Offset' Column calculations, you may Hard code CurrentDate for testing e.g. #date(2017,9,1)
CurrentDate = DateTime.Date(DateTime.FixedLocalNow()),
// Specify the last month in your Fiscal Year, e.g. if June is the last month of your Fiscal Year, specify 6
FiscalYearEndMonth = 6,
#"==SET PARAMETERS ABOVE==" = 1,
#"==Build Date Column==" = #"==SET PARAMETERS ABOVE==",
ListDates = List.Dates(StartDate, Number.From(EndDate - StartDate)+1, #duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(ListDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
// As far as Power BI is concerned, the 'Date' column is all that is needed 🙂 But we will continue and add a few Human-Friendly Columns
#"Changed Type to Date" = Table.TransformColumnTypes(#"Renamed Columns as Date",{{"Date", type date}}),
#"==Add Calendar Columns==" = #"Changed Type to Date",
then [MonthNum] - FiscalYearEndMonth
else [MonthNum] + (12 - FiscalYearEndMonth), type number),
Text.End(
Text.From(
if [MonthNum] > FiscalYearEndMonth
then [Year] + 1
else [Year]
)
, 2
)),

// Can be used to for example to show the past 3 months(CurMonthOffset = 0, -1, -2)
#"Added CurMonthOffset" = Table.AddColumn(#"==Add Calendar Date Offset Columns==", "CurMonthOffset", each ( Date.Year([Date]) - Date.Year(CurrentDate) ) * 12
+ Date.Month([Date]) - Date.Month(CurrentDate), Int64.Type),
// Can be used to for example to show the past 3 quarters (CurQuarterOffset = 0, -1, -2)
( Date.Year([Date]) - Date.Year(CurrentDate) )*4
/*Quarter Difference*/
+ Number.RoundUp(Date.Month([Date]) / 3)
- Number.RoundUp(Date.Month(CurrentDate) / 3),
Int64.Type),
// Can be used to for example to show the past 3 years (CurYearOffset = 0, -1, -2)
// Can be used to for example filter out all future dates
#"Added FutureDate Flag" = Table.AddColumn(#"Added CurYearOffset", "FutureDate", each if [Date] > CurrentDate then "Future" else "Past" ),
// FiscalYearOffset is the only Offset that is different.
// FiscalQuarterOffset = is same as CurQuarterOffset
// FiscalMonthOffset = is same as CurMonthOffset
#"Filtered Rows to CurrentDate" = Table.SelectRows(#"==Add FiscalYearOffset==", each ([Date] = CurrentDate)),
CurrentFiscalYear = #"Filtered Rows to CurrentDate"{0}[FiscalYear],
Number.From(Text.Range(CurrentFiscalYear,2,2))
/*Extract the numerical portion, e.g. FY18 = 18*/),
// Used as 'Sort by Column' for MonthYear columns
#"Added MonthYearNum" = Table.AddColumn(#"==Add General Columns==", "MonthYearNum", each [Year]*100 + [MonthNum] /*e.g. Sep-2016 would become 201609*/, Int64.Type),
then "Weekend"
else "Weekday"),
#"==Improve Ultimate Table" = #"Added WeekdayWeekend",
#"----Add WeekSequenceNum----" = #"==Improve Ultimate Table",
#"Filtered Rows Sundays Only (Start of Week)" = Table.SelectRows(#"----Add WeekSequenceNum----", each ([WeekdayNum] = 0)),
#"Added Index WeekSequenceNum" = Table.AddIndexColumn(#"Filtered Rows Sundays Only (Start of Week)", "WeekSequenceNum", 2, 1),
#"Merged Queries Ultimate Table to WeekSequenceNum" = Table.NestedJoin(#"==Improve Ultimate Table",{"Date"},#"Added Index WeekSequenceNum",{"Date"},"Added Index WeekNum",JoinKind.LeftOuter),
#"Expanded Added Index WeekNum" = Table.ExpandTableColumn(#"Merged Queries Ultimate Table to WeekSequenceNum", "Added Index WeekNum", {"WeekSequenceNum"}, {"WeekSequenceNum"}),
// somehow it ends up being unsorted after Expand Column, should not matter for the end table, but makes it harder to debug and check everything is correct. Thus sorting it.
#"ReSorted Rows by Date" = Table.Sort(#"Expanded Added Index WeekNum",{{"Date", Order.Ascending}}),
#"Filled Down WeekSequenceNum" = Table.FillDown(#"ReSorted Rows by Date",{"WeekSequenceNum"}),
#"Replaced Value WeekSequenceNum null with 1" = Table.ReplaceValue(#"Filled Down WeekSequenceNum",null,1,Replacer.ReplaceValue,{"WeekSequenceNum"}),
#"----WeekSequenceNum Complete----" = #"Replaced Value WeekSequenceNum null with 1",
Current_WeekSequenceNum = #"----WeekSequenceNum Complete----"{[Date = CurrentDate]}?[WeekSequenceNum],
#"Added Custom CurWeekOffset" = Table.AddColumn(#"----WeekSequenceNum Complete----", "CurWeekOffset", each [WeekSequenceNum] - Current_WeekSequenceNum, Int64.Type)
in

create the relationship required.

this date table has a current quarter offset column, set it to less than or equal to -1.

let me know if this resolves the issue.

Appreciate a thumbs up if this resolves the issue.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors