Power BI DAX: ISFILTERED, ERROR, CALCULATE, SUM, DATEADD, DIVIDE

Sales QoQ% =
IF (
    ISFILTERED ( 'financials'[Date] ),
    ERROR ( "Uh oh." ),
    VAR PREV_QUARTER =
        CALCULATE (
            SUM ( 'financials'[Sales] ),
            DATEADD ( 'financials'[Date], -1, QUARTER )
        )
    RETURN
        DIVIDE ( SUM ( 'financials'[Sales] ) - PREV_QUARTER, PREV_QUARTER )
)

This DAX expression calculates the percentage change in sales from the previous quarter to the current quarter, ensuring that the calculation is only performed when the 'Date' column is not filtered. If the 'Date' column is filtered, it returns an error message.

Detailed Explanation:

1. IF(ISFILTERED('financials'[Date]), ERROR("Uh oh."), ...):

   • This checks if the 'Date' column in the 'financials' table is filtered. If it is, it returns an error message "Uh oh." This is a safeguard to ensure the calculation is not performed on a filtered date context.

2. VAR PREV_QUARTER = CALCULATE(SUM('financials'[Sales]), DATEADD('financials'[Date], -1, QUARTER)):
   • This variable calculates the total sales for the previous quarter. The DATEADD function shifts the dates back by one quarter, and CALCULATE sums the sales for those dates.

3. RETURN DIVIDE(SUM('financials'[Sales]) - PREV_QUARTER, PREV_QUARTER):
   • This part calculates the QoQ percentage change. It subtracts the previous quarter's sales from the current quarter's sales and then divides by the previous quarter's sales to get the percentage change.