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

How to display last three quarters and QTD for current Quarter in Column chart

Hello everyone,

 

I have a requirement where in a hierarchy slicer with Year/Qtr/Month, user needs multi-select option and in one of the charts, user needs to have Last three Quarters and QTD for current quarter (in total 4 bars) to be shown in a Bar Chart. As there is Multi-Select option on the date slicer, Quartes need to be determined dynamically based on the maximum period selected in the slicer. Please help.

 

Thanks

Vijay

17 REPLIES 17
Super User
Super User

Re: How to display last three quarters and QTD for current Quarter in Column chart

Hi,

 

Share a datset and show the expected result.

vijaykvs Frequent Visitor
Frequent Visitor

Re: How to display last three quarters and QTD for current Quarter in Column chart

Hello Ashish,

 

Here is our hierarchy slicer where user can select multiple quarters and sample revenue data. If user selects FY2018-Q1 and FY2018-Q2, expectation is that column chart should show four Quarters (FY2017-Q3, FY2017-Q4, FY2018-Q1, FY2018-Q2) in X-

axis with respective revenue numbers.

 

Similarly if user selects entire FY2018, FY2018-Q1 to FY2018-Q4 should be shown. i.e. At any point, maximum quarter from the selected values should be considered and fours quarters data should be shown backwards.

 

 

Slicer.PNGData.PNG

Thanks,

Vijay

ManNVSM Member
Member

Re: How to display last three quarters and QTD for current Quarter in Column chart

Dear Vijay,

 

Your requirement need a Paramter table. Can you send me your pbix file? I will do on your pbix and send back after finish.

 

Regards,

ManNVSM.

vijaykvs Frequent Visitor
Frequent Visitor

Re: How to display last three quarters and QTD for current Quarter in Column chart

Dear ManNVSM,

 

Our original data is connected to a tabular cube with Live connection and reports are shared with user over Power BI service. Not sure if parameters will be helpful in this scenario but any insights would be helpful.

 

Here is the link for pbix file with sample data as shown in original post. Sample PBIX File

 

Thanks

Vijay

ManNVSM Member
Member

Re: How to display last three quarters and QTD for current Quarter in Column chart

Dear Vijay,

 

I got your pbix file. I'm on working now, I'll test and post the result (Yes / No) later Smiley Happy

 

Regards,

ManNVSM.

Super User
Super User

Re: How to display last three quarters and QTD for current Quarter in Column chart

Hi @vijaykvs,

 

In the Revenue table of your sample file, you have quarter wise revenue.  Is this actually how your data is?  Either i'd like to see a date column there or i should be able to generate a date column if you can supply Month and Year there as well.  Do you have that information.  Also, what is your Financial year?

vijaykvs Frequent Visitor
Frequent Visitor

Re: How to display last three quarters and QTD for current Quarter in Column chart

Hi Ashish,

 

I just created a sample file to simplify the problem. We have our data at Month grain. I tried to add date column in updated sample file at link below. Our financial year is from Jul - June.

 

Sample PBIX File v1

 

Thanks

Vijay

Community Support Team
Community Support Team

Re: How to display last three quarters and QTD for current Quarter in Column chart

@vijaykvs,

 

Make the relationship inactive and use measure below.

Measure =
VAR i =
    MAX ( MSFiscalYear[Index] )
VAR j =
    MAX ( Revenue[DateIndex] )
RETURN
    IF ( j <= i && j > i - 4, SUM ( Revenue[Revenue] ) )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: How to display last three quarters and QTD for current Quarter in Column chart

Hi @vijaykvs,

 

I still do not have enough information in the Revenue Table to build a column of Dates.