Forum Discussion
Filter in variable being applied to main query
Hi, I'm building a report where I am trying to show the latest months sales value against the sales value from the previous year. I have the below DAX expression:
VAR TimeBkey = CALCULATE(MAX('factFinance'[Time_BKey]), 'factFinance'[Value]<> 0, 'dimCategory'[Category_BKey] = "CUR_YEAR")
RETURN
CALCULATE(SUM('factFinance'[Value]), 'factFinance'[Time_BKey] = TimeBkey, ALL('dimCategory'))
The idea is to get the latest month for which there is data in the current year as a variable which is then used to filter the rest of the data. I then use a Clustered column chart with category as the legend to show the current value against the previous years' value. The problem is that the "CUR_YEAR" filter on dimCategory is still being applied, despite having ALL(dimCategory) as a filter. As a result only current year values are being shown. Any ideas on how I can fix this? Thanks
VAR TimeBkey = CALCULATE(MAX('factFinance'[Time_BKey]), 'factFinance'[Value]<> 0, 'dimCategory'[Category_BKey] = "CUR_YEAR")
RETURN
CALCULATE(SUM('factFinance'[Value]), 'factFinance'[Time_BKey] = TimeBkey, ALL('dimCategory'))
The idea is to get the latest month for which there is data in the current year as a variable which is then used to filter the rest of the data. I then use a Clustered column chart with category as the legend to show the current value against the previous years' value. The problem is that the "CUR_YEAR" filter on dimCategory is still being applied, despite having ALL(dimCategory) as a filter. As a result only current year values are being shown. Any ideas on how I can fix this? Thanks
1 Reply
v-frfei-msftCommunity Support
Hi RobH ,
How about updating your formula as below?
CALCULATE(SUM('factFinance'[Value]), filter(all('factFinance'),'factFinance'[Time_BKey] = TimeBkey))If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.