Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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 =
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
not, it has not
To dynamically adjust the ALLSELECTED and ORDERBY arguments based on the slicer selection, you can use a calculation group. Here's how:
-- 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)
)
)
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:
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
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?
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!!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
8 | |
8 | |
8 | |
6 |
User | Count |
---|---|
14 | |
12 | |
11 | |
9 | |
9 |