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.
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.
Your requirement need a Paramter table. Can you send me your pbix file? I will do on your pbix and send back after finish.
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
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?
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.
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] ) )