Highlight the most and least sales range on the line chart

[zzcc]

Hi everyone,

I have encountered a problem and hope to get ideas from here.
I have a sales table that records the date of sales and the amount of sales.
I want to show my sales trend on the line chart. In addition to the date slicer, I want a slicer to dynamically display the least and most sales in the past X days (30 days), and this range (30 days) need be highlighted on the line chart.
Below is the visual I expected.

Can anyone help me, any ideas are greatly appreciated, thanks in advance.

Best Regards

Zerrick

[Fowmy]

@zzcc 

Trying to understand your question. If you select 30 Days from the slicer, do you want to highlight the period that recorded the lowest sales and another highlighting for the highest sales?

[zzcc]

@Fowmy 

yes, i want to show the lowest sales and  the highest sales in 30 days.

In other words, I want to see the date that has the lowest cumulative sales in the last 30 days. For example, if the cumulative sales from July 1 to July 30 are the smallest or largest of all 30-day cumulative sales, then I would like to highlight this date range on the line chart.

Any ideas? Thanks in advance.

[Fowmy · Accepted solution]

@zzcc 

I made a solution using a sample file. I have attached the PBIX file below my signature.

The measure that identifies the Least and the highest cumulative periods. You can use it as conditional formatting.

Least and Highest Range = 
VAR __DAYS = 30
VAR __CURRDATE = MAX(Dates[Date] )
VAR T1 =
    ADDCOLUMNS (
        ALLSELECTED( Dates[Date] ),
        "@Sales", [Total Sales],
        "@CUMM",
            VAR __30DAYSBACK = Dates[Date] - __DAYS
            VAR __PERIOD =
                DATESBETWEEN ( Dates[Date], __30DAYSBACK+1 , Dates[Date] )
                RETURN
                    IF ( COUNTROWS ( __PERIOD ) >= __DAYS, CALCULATE ( [Total Sales], __PERIOD ) )
    )
VAR __MINVALUE =  MINX ( T1, [@CUMM] )
VAR __MAXVALUE =  MAXX ( T1, [@CUMM] )
VAR __MINDATE =  MAXX( FILTER( T1 , [@CUMM] = __MINVALUE ) , Dates[Date] )
VAR __MAXDATE =  MAXX( FILTER( T1 , [@CUMM] = __MAXVALUE ) , Dates[Date] )
RETURN
    SWITCH(
        TRUE(),
        __MINDATE >= __CURRDATE && __MINDATE - __DAYS <= __CURRDATE  , -1,
        __MAXDATE >= __CURRDATE && __MAXDATE - __DAYS <= __CURRDATE  , 1
    )

.