How to use SWITCH() in the relation parameter of OFFSET() function

[witheringarc5]

context:
I'm calculating YOY for different KPI, I'll use [Gross Sales] as example. I want the measure to calculate [Gross Sales] which offsets based on the selection of a slicer or field parameter named as "Fiscal Type". The currently working measure looks like this (does not automatically change how it offsets based on slicer or field parameter):

gross_sales_yoy =

VAR _offset =

CALCULATE(

    [Gross Sales],

    OFFSET(

        -1,

        ALLSELECTED('fiscal calendar'[fiscal_year]),

        ORDERBY('fiscal calendar'[fiscal_year], ASC)

    )

)

VAR diff =

IF(

    ISBLANK(_offset),

    BLANK(),

    [Gross Sales] - _offset

)

VAR yoy =

IF(

    ISBLANK(_offset),

    BLANK(),

    diff/_offset

)

RETURN

yoy

target outcome:
if fiscal_month is chosen in a slicer or field parameter, I want this part of the measure to automatically change like this
before:
ALLSELECTED('fiscal calendar'[fiscal_year]),

ORDERBY('fiscal calendar'[fiscal_year], ASC)

after:

ALLSELECTED('fiscal calendar'[fiscal_month]),

ORDERBY('fiscal calendar'[fiscal_month], ASC)

failed method 1:

I've tried using SWITCH() in hopes of it will help me replace the code, example:

gross_sales_yoy = 

VAR selected_fiscal =

SELECTEDVALUE('Fiscal Type'[Fiscal Type])

VAR switch_allselected = 

SWITCH(

    selected_fiscal,

    "fiscal_year", "'fiscal calendar'[fiscal_year]",

    "fiscal_month", "'fiscal calendar'[fiscal_month]",

    "fiscal_day", "'fiscal calendar'[fiscal_day]"

)

VAR switch_orderby = 

SWITCH(

    selected_fiscal,

    "fiscal_year", "'fiscal calendar'[fiscal_year]",

    "fiscal_month", "'fiscal calendar'[fiscal_month]",

    "fiscal_day", "'fiscal calendar'[fiscal_day]"

)

VAR _offset =

CALCULATE(

    [Gross Sales],

    OFFSET(

        -1,

        ALLSELECTED(switch_allselected),

        ORDERBY(switch_orderby, ASC)

    )

)

(remaining parts are the same)

outcome:

both ALLSELECTED() and ORDERBY() does not support variable

I tried to directly put SWITCH() inside ALLSELECTED() but it says "Parameter is not the correct type", also tried to use ALLSELECTED('fiscal type'[fiscal type]) and ORDERBY('fiscal type'[fiscal type]) but it doesn't offset correctly.

I can't provide any data or pbix files because of confidentiality, but [Gross Sales] is basically like SUM(table_name[amount]) and you can treat fiscal year/month/day as a normal calendar year/month/day. Please provide help if you have any idea, thanks.

p.s. Bookmark is not preferred due to business reasons, I'm planning to try using visual calculation and calculation group as well to compare their pros and cons.

[Anonymous]

Hi @witheringarc5 

Has your problem been resolved? If so, could you mark the corresponding reply as the solution so that others with similar issues can benefit from it?

Best Regards,

Jayleny

[witheringarc5]

not, it has not

[Poojara_D12]

Hi @witheringarc5 

To dynamically adjust the ALLSELECTED and ORDERBY arguments based on the slicer selection, you can use a calculation group. Here's how:

• Steps:
  • Create a Calculation Group in Tabular Editor:
    • Name it Fiscal Offset.
    • Add a Calculation Item for each fiscal type (fiscal_year, fiscal_month, fiscal_day).
    • Define the expression for each item.

-- For Fiscal Year
SELECTEDMEASURE() * 1

-- For Fiscal Month
CALCULATE(
    SELECTEDMEASURE(),
    OFFSET(
        -1,
        ALLSELECTED('fiscal calendar'[fiscal_month]),
        ORDERBY('fiscal calendar'[fiscal_month], ASC)
    )
)

-- For Fiscal Day (example)
CALCULATE(
    SELECTEDMEASURE(),
    OFFSET(
        -1,
        ALLSELECTED('fiscal calendar'[fiscal_day]),
        ORDERBY('fiscal calendar'[fiscal_day], ASC)
    )
)

• Update Your Measure to Use the Calculation Group:

gross_sales_yoy =
VAR _offset =
    CALCULATE(
        [Gross Sales],
        'Fiscal Offset'[Fiscal Offset Calculation]
    )
VAR diff =
    IF(ISBLANK(_offset), BLANK(), [Gross Sales] - _offset)
RETURN
    IF(ISBLANK(_offset), BLANK(), diff / _offset)

Link the Calculation Group to the Field Parameter/Slicer:

• Ensure the slicer is connected to the Fiscal Offset calculation group. This will dynamically adjust the context based on the selection.

This approach allows you to maintain flexibility and dynamically adjust without requiring variables that AllSelected or

ORDER BY do not support.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[witheringarc5]

I have created the calculation group and updated the measure, when I drag the updated measure to the values section of the matrix, it shows error: MdxScript(Model) (464,5) Calculation error in measure 'Calculation Group'[gross_sales_yoy]: Cannot convert value 'fiscal_year' of type Text to type True/False. Why is that?

[VahidDM]

Hi @witheringarc5 

You're getting this error because somewhere in your calculation group or measure logic, you're using a text value (e.g., "fiscal_year") in a place that expects a True/False (boolean) expression. Double-check your conditional statements (IF or SWITCH) to ensure that each condition returns a boolean, not text. For instance, use IF( SELECTEDVALUE('Table'[Column]) = "fiscal_year", ... ) rather than trying to treat "fiscal_year" directly as a boolean.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube