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

[vijaykvs]

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

[Ashish_Mathur]

Hi,

Share a datset and show the expected result.

[vijaykvs]

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.

Thanks,

Vijay

[Anonymous]

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]

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

[Ashish_Mathur]

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]

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

[Ashish_Mathur]

Hi @vijaykvs,

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

[vijaykvs]

@Ashish_Mathur I have already provided date column in the updated sample and linked revenue table with the same. Please let me know what else you are looking for?

Thanks

Vijay

[Ashish_Mathur]

Hi @vijaykvs,

In the revenue table, where is the date column?  I just see a dateindex column but i do not know what to do with it.  There should be a propoer date column in the revenue table.

[vijaykvs]

Hi @Ashish_Mathur 

As you mentioned Revenue table has DateIndex and it is pointing to MSFiscalYearTable which has actual date column as well. Cant you get it from there?

Thanks

Vijay

[v-chuncz-msft]

@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] ) )

[vijaykvs]

Hey, I am able to calculate revenue amount dymanically based on slicer selection. But my goal is to have axis in the column chart populated dynamically as well with last four quarters based on maximum period selected in slicer. Tried using calcualted table as well but didnt't work as they are calculated at query refresh time and not dynamically upon selection of slicer.

Thanks

Vijay

[v-chuncz-msft]

@vijaykvs,

I've created the measure above. Check it, please.

[vijaykvs]

@v-chuncz-msft Measure will not be helpful as we need to show this information in a bar chart and last four quartes needs to be displayed dynamically in the chart. Dont think we can use measures to populate Axis in charts unless I am missing something.

Thanks

Vijay

[Anonymous]

Dear @vijaykvs,

We can not make a Dynamic column. 

- First: only Measure can refresh data after selected a fiter. So when you make a Dynamic column, it'll show the first value at the firs time data load and won't change.

- Second: Measure can not show as Axix or Legend, it only place in "Value" in chart.

Hope someone can resolve it 🙂

Thanks & Regards,

ManNVSM.

[v-chuncz-msft]

@vijaykvs,

It works. Just drag that measure to Value.

[Anonymous]

Dear Vijay,

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

Regards,

ManNVSM.