cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
thalitakadlec Frequent Visitor
Frequent Visitor

Bar chart by quarter including last quarter from previous year

Hi there,

I currently have a report that shows me a selected year's metrics by quarter on a bar chart:

 

currentchart.jpg

filters.jpg

However, I would like this view to also show me a bar for the last quarter of the previous year for comparison, that would still show up even if I have a page level filter for Year.

For example: if I filter my page to the year of 2018, I still want to see the last quarter of 2017 on my bar chart, to the left of the 2018 quarters bars, ideally looking something like this:

 

expectedchart.jpg

 

Is this possible? How can I achieve this or something similar that serves the same purpose?

Thanks in advance!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
vaibhavdesai Established Member
Established Member

Re: Bar chart by quarter including last quarter from previous year

Hi,

Follow below steps to achieve this:

1. Create a Year Table with one column having just the different years from your calendar.

Create a slicer from this column. My model contains 2 years, so i have a table called YearTable with 2 rows and 1 once column

 

2. Create below measures:

Selected Year = SELECTEDVALUE( YearTable[Year] )

Last Quarter Flag = 
VAR v_PreviousYear = [Selected Year] - 1
VAR v_MaxYear = CALCULATE( MAX( 'Calendar'[Year] ) )
VAR v_MaxPreviousUear = v_MaxYear - 1
VAR v_MaxQuarter = CALCULATE( MAX( 'Calendar'[Quarter] ) )
RETURN
IF ( ( v_MaxYear == [Selected Year] ), "1", IF( (v_MaxYear == v_PreviousYear) && (v_MaxQuarter == 4), "1", "0" ))

3. If you don't have a column for 'Quarter & Year', create it as:

Quarter & Year = "Qtr-" & 'Calendar'[Quarter] & " " & 'Calendar'[Year]

4. Drag Quarter & year column on X axis and your measure on the values part on a stacked column chart like in the image shown in previous message.

5. Add a visual level filter for this chart as below: (Last Quarter Flag = 1)

result.PNG

Thanks.

View solution in original post

5 REPLIES 5
vaibhavdesai Established Member
Established Member

Re: Bar chart by quarter including last quarter from previous year

Here's a link to pbix that i created:

After selecting 1998 as Year, it is showing all quarters of 1998 and Q4 of 1997.

https://1drv.ms/u/s!AikPceQOhqFEhA99DNyDpoZX8MYG?e=MGSHZD

result.PNG

Thanks.

thalitakadlec Frequent Visitor
Frequent Visitor

Re: Bar chart by quarter including last quarter from previous year

Hi @vaibhavdesai !

Thanks for your reply.

However I unfortunately cannot access your file due to company security barriers regarding online file storage sites.

Would it be possible for you to explain how you created the view and maybe upload some pictures here of how you set this up in PB?

Sorry for the inconvenience and thanks again!

vaibhavdesai Established Member
Established Member

Re: Bar chart by quarter including last quarter from previous year

Hi,

Follow below steps to achieve this:

1. Create a Year Table with one column having just the different years from your calendar.

Create a slicer from this column. My model contains 2 years, so i have a table called YearTable with 2 rows and 1 once column

 

2. Create below measures:

Selected Year = SELECTEDVALUE( YearTable[Year] )

Last Quarter Flag = 
VAR v_PreviousYear = [Selected Year] - 1
VAR v_MaxYear = CALCULATE( MAX( 'Calendar'[Year] ) )
VAR v_MaxPreviousUear = v_MaxYear - 1
VAR v_MaxQuarter = CALCULATE( MAX( 'Calendar'[Quarter] ) )
RETURN
IF ( ( v_MaxYear == [Selected Year] ), "1", IF( (v_MaxYear == v_PreviousYear) && (v_MaxQuarter == 4), "1", "0" ))

3. If you don't have a column for 'Quarter & Year', create it as:

Quarter & Year = "Qtr-" & 'Calendar'[Quarter] & " " & 'Calendar'[Year]

4. Drag Quarter & year column on X axis and your measure on the values part on a stacked column chart like in the image shown in previous message.

5. Add a visual level filter for this chart as below: (Last Quarter Flag = 1)

result.PNG

Thanks.

View solution in original post

thalitakadlec Frequent Visitor
Frequent Visitor

Re: Bar chart by quarter including last quarter from previous year

 

Hey @vaibhavdesai 

 

Thanks so much for your help.

It seems I am almost there, can you please provide some insights on these 2 issues I am still having?

 

1) I was able to get up until step 4 from your instructions. However, when I tried to apply the filter for "Last Quarter Flag is 1", the following message popped up.  Any ideas how to fix this?

 

quarterbars.jpg

 

error.jpg

 

2) Will I be able to sort the bars in chronological order? How?

 

Thanks again!

vaibhavdesai Established Member
Established Member

Re: Bar chart by quarter including last quarter from previous year

Can you share the DAX written for the flag measure. Even better if you could share the pbix file.
Thanks.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 285 members 2,671 guests
Please welcome our newest community members: