Forum Discussion

abanar2's avatar
abanar2
Icon for Helper I rankHelper I
1 year ago
Solved

Need help with my measure

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_...
Need Help
  • pankajnamekar25's avatar
    pankajnamekar25
    1 year ago

    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.

     

wardy912's avatar
wardy912
Icon for Super User rankSuper User
1 year ago

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's avatar
abanar2
Icon for Helper I rankHelper I
1 year ago

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's avatar
    pankajnamekar25
    Icon for Super User rankSuper User
    1 year ago

    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.