Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!!
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |