Need help with my measure

[abanar2]

Dear All,

I’m working with a Calendar table that includes the following columns:

DayDate	Year	Querter_Num	Quarter_Text	Year-Quarter	Week_of_the_Year_Num	YearWeek_Text	Month_Num	Start_of_Quarter	End_of_Quarter
3-Jan-10	2010	1	Q1	2010-Q1	1	2010- W1	1	1-Jan-10	31-Mar-10
2-Jan-10	2010	1	Q1	2010-Q1	1	2010- W1	1	1-Jan-10	31-Mar-10
1-Jan-10	2010	1	Q1	2010-Q1	1	2010- W1	1	1-Jan-10	31-Mar-10
4-Jan-10	2010	1	Q1	2010-Q1	2	2010- W2	1	1-Jan-10	31-Mar-10

I’m using the Year-Quarter column as a slicer on my report page.

There is an active relationship between the Calendar[DayDate] and Item_Exp[Date_closed] columns.

I want to create a dynamic line chart that shows the values of a measure ( _m_cnt_closed_item ) over the last 5 quarters, ending with the quarter selected in the slicer.

So, if the user selects 2023-Q4, the chart should display data from 2022-Q4 to 2023-Q4.

Currently, when I use the Year-Quarter slicer, the line chart only shows data for the selected quarter, and exclude the previous 4 quarters.

This is my measure:

_m_cnt_closed_last_5_quarter =
VAR MaxDate = MAX('99999_Calendar'[DayDate])
VAR MinDate = EOMONTH(MaxDate, -15)

RETURN
CALCULATE(
    [_m_cnt_closed_item],
    'Item_Exp'[Date_closed] > MinDate,
    'Item_Exp'[Date_closed] <= MaxDate,
    REMOVEFILTERS( 'Item_Exp'[Date_closed] )
)

How can I modify this measure (or the slicer logic) so that the line chart dynamically shows the last 5 quarters based on the selected quarter in the slicer?

[v-sdhruv]

Hi @abanar2 ,

Just wanted to check if you had the opportunity to review the suggestions provided and able to resolve the issue?
If you still have any questions, feel free to reach out.

Thank you

[v-sdhruv]

Hi @abanar2 ,

Just wanted to check if you had the opportunity to review the suggestions provided and able to resolve the issue?

[v-sdhruv]

Hi @abanar2 ,

Just wanted to check if you had the opportunity to review the suggestions provided?
Were you able to resolve the issue?

[wardy912]

Hi @abanar2 

 You need to create a new table for the slicer, do not relate it to the calendar

Slicer_YearQuarter = DISTINCT('99999_Calendar'[Year-Quarter])

Next, create a measure to get the max quarter selected

Selected_Quarter = 
MAX('Slicer_YearQuarter'[Year-Quarter])

Modify your measure as follows

_m_cnt_closed_last_5_quarter =
VAR SelectedQtr = MAX('Slicer_YearQuarter'[Year-Quarter])
VAR SelectedYear = VALUE(LEFT(SelectedQtr, 4))
VAR SelectedQtrNum = VALUE(RIGHT(SelectedQtr, 1))

-- Generate a list of last 5 quarters
VAR Last5Quarters =
    ADDCOLUMNS(
        GENERATESERIES(0, 4, 1),
        "Year", SelectedYear - INT((SelectedQtrNum - [Value]) < 1),
        "Quarter", MOD(SelectedQtrNum - [Value] - 1 + 4, 4) + 1
    )

-- Convert to Year-Quarter format
VAR Last5QtrText =
    SELECTCOLUMNS(
        Last5Quarters,
        "Year-Quarter", FORMAT([Year], "0000") & "-Q" & [Quarter]
    )

RETURN
CALCULATE(
    [_m_cnt_closed_item],
    TREATAS(Last5QtrText, '99999_Calendar'[Year-Quarter])
)

This setup ensures that the slicer only drives the logic of the measure, not the visual filter context, allowing the chart to show the last 5 quarters dynamically.

Please give a thumbs up and mark as solved if this helps, thanks!

[abanar2]

Hello,

The Copilot recommended me the something similar, but I am not convinced about its performance.

May I ask if you have different ideas?

[pankajnamekar25 · Accepted solution]

Hello @abanar2 

Try this approch

Extract Selected Quarter Info

We'll extract selected year and quarter from the slicer selection.

VAR SelectedQuarterText = SELECTEDVALUE('99999_Calendar'[Year-Quarter])

VAR SelectedYear = VALUE(LEFT(SelectedQuarterText, 4))

VAR SelectedQtrNum = VALUE(RIGHT(SelectedQuarterText, 1))

 Generate Last 5 Quarters List

Use logic to generate year-quarter values of the last 5 quarters.

VAR Last5Quarters =

    ADDCOLUMNS(

        GENERATESERIES(0, 4, 1),

        "QtrOffset", [Value],

        "Year", SelectedYear - INT((SelectedQtrNum - [Value]) < 1),

        "Quarter", MOD(SelectedQtrNum - [Value] - 1 + 4, 4) + 1

    )

VAR Last5QuarterText =

    SELECTCOLUMNS(

        Last5Quarters,

        "Year-Quarter", FORMAT([Year], "0000") & "-Q" & [Quarter]

    )

Final Measure

_m_cnt_closed_last_5_quarter =

VAR SelectedQuarterText = SELECTEDVALUE('99999_Calendar'[Year-Quarter])

VAR SelectedYear = VALUE(LEFT(SelectedQuarterText, 4))

VAR SelectedQtrNum = VALUE(RIGHT(SelectedQuarterText, 1))

VAR Last5Quarters =

    ADDCOLUMNS(

        GENERATESERIES(0, 4, 1),

        "QtrOffset", [Value],

        "Year", SelectedYear - INT((SelectedQtrNum - [Value]) < 1),

        "Quarter", MOD(SelectedQtrNum - [Value] - 1 + 4, 4) + 1

    )

VAR Last5QuarterText =

    SELECTCOLUMNS(

        Last5Quarters,

        "Year-Quarter", FORMAT([Year], "0000") & "-Q" & [Quarter]

    )

RETURN

CALCULATE(

    [_m_cnt_closed_item],

    KEEPFILTERS(TREATAS(Last5QuarterText, '99999_Calendar'[Year-Quarter]))

Thanks

 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.