Forum Discussion
Help help - Dax correction
Hi JothiG,
Thank you FarhanJeelani, for your insights.
I reproduced your issue with sample data and was able to create the issue in Power BI. I made the chart and verified the results. I've attached the PBIX file for your reference.
Last Month Value = VAR _yr = MAX('yarn data'[Stock_finyear]) VAR _max_month = CALCULATE ( MAX ( 'yarn data'[month_sorter] ), FILTER ( ALL ( 'yarn data' ), 'yarn data'[Stock_finyear] = _yr ) ) RETURN CALCULATE ( SUM ( 'yarn data'[Value] ), 'yarn data'[Stock_finyear] = _yr, 'yarn data'[month_sorter] = _max_month )Thank you.
Hi JothiG ,
The issue is that sum_val is summing all months in the year (and you even have an invalid filter expression 'yarn data'[value]).
You need to filter the sum to only the last month of that year.
You can try the approach that returns the value for the last month of the current year context (and works well when the visual shows Year on the axis).
Using SELECTEDVALUE (works well when Year is in the visual axis)
Last Month Value = VAR _yr = SELECTEDVALUE('yarn data'[Stock_finyear]) VAR _lastMonth = CALCULATE( MAX('yarn data'[month_sorter]), ALL('yarn data'), 'yarn data'[Stock_finyear] = _yr ) RETURN IF( NOT ISBLANK(_yr), CALCULATE( SUM('yarn data'[value]), ALL('yarn data'), 'yarn data'[Stock_finyear] = _yr, 'yarn data'[month_sorter] = lastMonth ), BLANK() )or,
If you prefer MAX context (works similarly when there’s a single year in context)
Last Month Value = VAR _yr = MAX('yarn data'[Stock_finyear]) VAR _lastMonth = CALCULATE( MAX('yarn data'[month_sorter]), ALL('yarn data'), 'yarn data'[Stock_finyear] = _yr ) RETURN CALCULATE( SUM('yarn data'[value]), ALL('yarn data'), 'yarn data'[Stock_finyear] = _yr, 'yarn data'[month_sorter] = _lastMonth )
Please mark this post as solution if it helps you. Appreciate Kudos.
it is also not working ...
