Show Previous 4 Quarter on Trend Chart based on current Selection

[akmalquamri]

Hi All,

Stuck at a point where my requirement is show previous 4 quarter based on filter selection in a line chart.

Suppose filter year is selected 2024 and month selected is Mar, which is Q4-2024. This Q4-2024 plus previous 4 quarter to this selection which should be Q3-2024, Q2-2024, Q1-2024 and Q4-2023.

Below is my approach:

Dimension: CalanderQuarter &'-'& Right(fYear,2)

Measure: Sum(
                         {<
                            Date={">=$(=QuarterStart(AddMonths(Max(CalendarDate),-16))))

                            <=$(=Date(Max(CalendarDate))))"},
                                    CalendarWeek=, CalendarDay=, FMonth=, FYear1=, fYear=
                         >} [Amount])

[SunilChauhan · Accepted solution]

use can try addYears also if you want to 

Measure: Sum(
                         {<
                            Date={">=$(=QuarterStart(AddYears(Max(CalendarDate),-1))))

                            <=$(=Date(Max(CalendarDate))))"},
                                    CalendarWeek=, CalendarDay=, FMonth=, FYear1=, fYear=
                         >} [Amount])

[rodrigo_martins]

Hi @akmalquamri 

Just for clarify:
March/2024 would be Q1/2024, so the quarters displayed in the chart would be Q1-2024 and the 4 previous ones: Q4-2023, Q3-2023, Q2-2023 and Q1-2023, right?

To solve this problem I would look at the calendar for that data model. Does your Dim_Calendar have a QuarterId field? His calculation would be Year * 4 + (Quarter - 1). The idea of ​​this field is to have a sequential numeric identifier for each quarter, so:

• Q1/2023 would be 2023 * 4 + (0) = 8092
• Q2/2023 would be 2023 * 4 + (1) = 8093
• Q3/2023 would be 2023 * 4 + (2) = 8094
• Q4/2023 would be 2023 * 4 + (3) = 8095
• Q1/2024 would be 2024 * 4 + (0) = 8096

This will allow you to create expressions in your SetAnalysis just by comparing the QuarterId field, more or less like this (I didn't test it, ok?):

Sum({<
QuarterId={">=$(=Max(QuarterId)-5)<=$(=Max(QuarterId))"},
Date= ,CalendarWeek=, CalendarDay=, FMonth=, FYear1=, fYear=>} [Amount])

Additionally, you could also create another field with the expression CalanderQuarter &'-'& Right(fYear,2), naming it QuarterYear (for example). This way you can reuse it more easily in other components of the application.
There are several examples of master calendar scripts in the community, you can take a look at this post.

Hope it helps!

[akmalquamri]

AddYears is not working with this I tried multiple times