Daily Sales Report handling , DAX for deviation for dynamic date range

Hi sriramk ,

For Date modified, you can refer @ ManuMMI 's post, about dax in this thread, if you do not want to change the formula frequently and achieve it dynamically, suggest that you can create a calendar table first and use it as a slicer under date slider so that you can change the date range as your need.

Table = CALENDARAUTO()

Create a measure to calculate the average:

Dyanamics last three days average = 
VAR _maxdate =
    CALCULATE ( MAX ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) )
VAR _mindate =
    CALCULATE ( MIN ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) )
RETURN
    IF (
        SELECTEDVALUE ( 'Status Table'[Date] ) >= _mindate
            && SELECTEDVALUE ( 'Status Table'[Date] ) <= _maxdate,
        CALCULATE (
            AVERAGE ( 'Status Table'[Sales] ),
            FILTER (
                ALL ( 'Status Table' ),
                'Status Table'[Date] >= _mindate
                    && 'Status Table'[Date] <= _maxdate
            )
        ),
        BLANK ()
    )

Create another measure to show the status:

Status = 
VAR _deviation =
    DIVIDE (
        SELECTEDVALUE ( 'Status Table'[Sales] ) - [Dyanamics last three days average],
        SELECTEDVALUE ( 'Status Table'[Sales] )
    )
RETURN
    IF (
        [Dyanamics last three days average] <> BLANK (),
        IF (
            _deviation < 0
                && ABS ( _deviation ) < 0.05,
            "poor",
            IF ( _deviation > 0, "good" )
        )
    )

If you also want to show the background in the status field, you can create an extra conditional format measure and apply it for the [Status] measure:

Conditional format background = 
SWITCH ( TRUE (), [Status] = "poor", "red", [Status] = "good", "green" )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.