Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I'm looking to show a measure to the same week in the previous fiscal year:
LY Stock (sgls) =
VAR currentFiscalYear =
MAX ( 'Stock & Sales'[fiscal_year] )
VAR currentFiscalWeek =
MAX ( 'Stock & Sales'[fiscal_week_number] )
RETURN
CALCULATE (
Sum('Stock & Sales'[stock_sgls]), ALLSELECTED('Stock & Sales'[bu]),
FILTER (
ALL ( 'Stock & Sales' ),
'Stock & Sales'[fiscal_year]
= currentFiscalYear - 1
&& 'Stock & Sales'[fiscal_week_number] = currentFiscalWeek
),FILTER (
ALL ( 'Stock & Sales' ),'Stock & Sales'[bu] = SELECTEDVALUE( ( 'Stock & Sales'[bu] ))))
However, when it's not filtered, it shows zero. I need it to show the total.
Can someone help, please?
Solved! Go to Solution.
Please try this expression instead. Not sure why you have the ALLSELECTED() in there and then filter it back down again with SELECTEDVALUE. In any case, the SELECTEDVALUE is why you were not seeing anything for total. I replaced it with VALUES. Also note that I updated your ALL to only include the columns being filtered (a good practice).
LY Stock (sgls) =
VAR currentFiscalYear =
MAX ( 'Stock & Sales'[fiscal_year] )
VAR currentFiscalWeek =
MAX ( 'Stock & Sales'[fiscal_week_number] )
RETURN
CALCULATE (
SUM ( 'Stock & Sales'[stock_sgls] ),
ALLSELECTED ( 'Stock & Sales'[bu] ),
FILTER (
ALL (
'Stock & Sales'[fiscal_year],
'Stock & Sales'[fiscal_week_number]
),
'Stock & Sales'[fiscal_year] = currentFiscalYear - 1
&& 'Stock & Sales'[fiscal_week_number] = currentFiscalWeek
),
VALUES ( 'Stock & Sales'[bu] )
)
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Excellent! Thank you.
Noted on the ALL requirement.
Please try this expression instead. Not sure why you have the ALLSELECTED() in there and then filter it back down again with SELECTEDVALUE. In any case, the SELECTEDVALUE is why you were not seeing anything for total. I replaced it with VALUES. Also note that I updated your ALL to only include the columns being filtered (a good practice).
LY Stock (sgls) =
VAR currentFiscalYear =
MAX ( 'Stock & Sales'[fiscal_year] )
VAR currentFiscalWeek =
MAX ( 'Stock & Sales'[fiscal_week_number] )
RETURN
CALCULATE (
SUM ( 'Stock & Sales'[stock_sgls] ),
ALLSELECTED ( 'Stock & Sales'[bu] ),
FILTER (
ALL (
'Stock & Sales'[fiscal_year],
'Stock & Sales'[fiscal_week_number]
),
'Stock & Sales'[fiscal_year] = currentFiscalYear - 1
&& 'Stock & Sales'[fiscal_week_number] = currentFiscalWeek
),
VALUES ( 'Stock & Sales'[bu] )
)
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
87 | |
84 | |
68 | |
63 | |
63 |
User | Count |
---|---|
208 | |
120 | |
113 | |
79 | |
72 |