Forum Discussion
Percentage Change From Previous Month
Hi Anonymous ,
Got it! The issue with the slicer selection not propagating correctly to the PreviousMonthValue likely stems from DATEADD not working as expected within the slicer’s filter context. Instead of using DATEADD, a more reliable approach is to use FILTER to ensure the correct previous month's value is retrieved dynamically while respecting the active selection. The revised formula first captures the selected month using MAX('DateTable'[Date]) and calculates the current month's success percentage using AVERAGE('Table'[Succes %]). To get the previous month's value, CALCULATE is used with FILTER(ALL('DateTable')), ensuring the previous month is determined dynamically using EOMONTH(SelectedMonth, -1). This method ensures that even if some months are missing in the dataset, the formula still retrieves the correct previous month’s value.PercentageChangeFromPreviousMonth = VAR SelectedMonth = MAX('DateTable'[Date]) VAR CurrentMonthValue = AVERAGE('Table'[Succes %]) VAR PreviousMonthValue = CALCULATE( AVERAGE('Table'[Succes %]), FILTER( ALL('DateTable'), 'DateTable'[Date] = EOMONTH(SelectedMonth, -1) ) ) VAR Change = CurrentMonthValue - PreviousMonthValue VAR PercentageChange = IF( NOT ISBLANK(PreviousMonthValue) && PreviousMonthValue <> 0, DIVIDE(Change, PreviousMonthValue, 0), BLANK() ) RETURN IF( NOT ISBLANK(PercentageChange), FORMAT(PercentageChange, "+0%;-0%;0%"), "N/A" )This formula ensures that the slicer selection dynamically affects the previous month's value while also maintaining proper aggregation of Succes %, assuming it is an average rate rather than a summable value. If there are still issues, verifying that DateTable[Date] is being used in the slicer and checking whether Succes % is at the correct granularity might be necessary. To debug further, creating a check for the previous month using MAX within a filtered context can help confirm that the formula is correctly retrieving the expected date.
PreviousMonthCheck = VAR SelectedMonth = MAX('DateTable'[Date]) RETURN CALCULATE( MAX('DateTable'[Date]), FILTER( ALL('DateTable'), 'DateTable'[Date] = EOMONTH(SelectedMonth, -1) ) )If this check does not return a valid previous month’s date, it may indicate that the slicer is not correctly applied or that DateTable is missing continuous months. In that case, adjusting the filter context or ensuring that Succes % calculations align with the time granularity should resolve the issue.
Best regards,
4 Replies
- DataNinja777
Super User
Hi Anonymous ,
Your DAX formula is close to being correct, but a few adjustments are needed to ensure it calculates the percentage change properly. One possible issue is that DATEADD might not be working as expected due to the filter context from the drill-through page. Additionally, 'Table'[DataRefreshMonth] must be a proper date column for DATEADD to function correctly. Another important consideration is how you are aggregating 'Succes %'. If this column already represents a percentage, summing it may not yield the expected results; using AVERAGE instead might be more appropriate depending on your data structure.A revised version of your DAX formula ensures that the previous month's value is correctly retrieved while maintaining relevant filters. It uses ALLEXCEPT to clear unnecessary filters while preserving the required context. Additionally, it includes a safeguard against division by zero.
PercentageChangeFromPreviousMonth = VAR CurrentMonthValue = SUM('Table'[Succes %]) VAR PreviousMonthValue = CALCULATE( SUM('Table'[Succes %]), ALLEXCEPT('Table', 'Table'[SomeKeyColumn]), DATEADD('Table'[DataRefreshMonth], -1, MONTH) ) VAR Change = CurrentMonthValue - PreviousMonthValue VAR PercentageChange = IF( NOT ISBLANK(PreviousMonthValue) && PreviousMonthValue <> 0, DIVIDE(Change, PreviousMonthValue, 0), BLANK() ) RETURN IF( NOT ISBLANK(PercentageChange), FORMAT(PercentageChange, "+0%;-0%;0%"), "N/A" )This formula ensures that the measure works correctly in the drill-through page by handling filter context issues. If DATEADD does not return expected results, check whether 'Table'[DataRefreshMonth] is in the correct date format. You can verify this by creating a measure to inspect the previous month’s value:
PreviousMonthCheck = CALCULATE( MAX('Table'[DataRefreshMonth]), DATEADD('Table'[DataRefreshMonth], -1, MONTH) )If this does not return a valid date, it might indicate an issue with the column’s format. You may also want to check whether SUM('Table'[Succes %]) is the appropriate aggregation method for your data. If Succes % represents an average rate rather than a summable value, using AVERAGE('Table'[Succes %]) instead might provide a more accurate calculation.
Best regards,
DataNinja777 Not giving a desired output.
Also I checked PreviousMonthCheck and it is resulting the previous month, but this is not changing according slicer selection of date. I mean if any month is selected, the previous month should also change, which is not the case.
- DataNinja777
Hi Anonymous ,
Got it! The issue with the slicer selection not propagating correctly to the PreviousMonthValue likely stems from DATEADD not working as expected within the slicer’s filter context. Instead of using DATEADD, a more reliable approach is to use FILTER to ensure the correct previous month's value is retrieved dynamically while respecting the active selection. The revised formula first captures the selected month using MAX('DateTable'[Date]) and calculates the current month's success percentage using AVERAGE('Table'[Succes %]). To get the previous month's value, CALCULATE is used with FILTER(ALL('DateTable')), ensuring the previous month is determined dynamically using EOMONTH(SelectedMonth, -1). This method ensures that even if some months are missing in the dataset, the formula still retrieves the correct previous month’s value.PercentageChangeFromPreviousMonth = VAR SelectedMonth = MAX('DateTable'[Date]) VAR CurrentMonthValue = AVERAGE('Table'[Succes %]) VAR PreviousMonthValue = CALCULATE( AVERAGE('Table'[Succes %]), FILTER( ALL('DateTable'), 'DateTable'[Date] = EOMONTH(SelectedMonth, -1) ) ) VAR Change = CurrentMonthValue - PreviousMonthValue VAR PercentageChange = IF( NOT ISBLANK(PreviousMonthValue) && PreviousMonthValue <> 0, DIVIDE(Change, PreviousMonthValue, 0), BLANK() ) RETURN IF( NOT ISBLANK(PercentageChange), FORMAT(PercentageChange, "+0%;-0%;0%"), "N/A" )This formula ensures that the slicer selection dynamically affects the previous month's value while also maintaining proper aggregation of Succes %, assuming it is an average rate rather than a summable value. If there are still issues, verifying that DateTable[Date] is being used in the slicer and checking whether Succes % is at the correct granularity might be necessary. To debug further, creating a check for the previous month using MAX within a filtered context can help confirm that the formula is correctly retrieving the expected date.
PreviousMonthCheck = VAR SelectedMonth = MAX('DateTable'[Date]) RETURN CALCULATE( MAX('DateTable'[Date]), FILTER( ALL('DateTable'), 'DateTable'[Date] = EOMONTH(SelectedMonth, -1) ) )If this check does not return a valid previous month’s date, it may indicate that the slicer is not correctly applied or that DateTable is missing continuous months. In that case, adjusting the filter context or ensuring that Succes % calculations align with the time granularity should resolve the issue.
Best regards,
- nilendraFabric
PercentageChangeFromPreviousMonth =
VAR CurrentMonthValue = [Succes %]
VAR PreviousMonthValue =
CALCULATE(
[Succes %],
PREVIOUSMONTH('DateTable'[Date])
)
VAR Change = CurrentMonthValue - PreviousMonthValue
RETURN
IF(
NOT ISBLANK(PreviousMonthValue),
FORMAT(
DIVIDE(Change, PreviousMonthValue, BLANK()),
"0%;-0%;0%"
),
"N/A"
)
