Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
zzcc
Frequent Visitor

Highlight the most and least sales range on the line chart

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.

zzcc_0-1629371957916.png

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

 

 

Best Regards

Zerrick

1 ACCEPTED SOLUTION

@zzcc 

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

Fowmy_0-1629470819169.png

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
    )

 

 


.



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

3 REPLIES 3
Fowmy
Super User
Super User

@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?

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

zzcc
Frequent Visitor

@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.

@zzcc 

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

Fowmy_0-1629470819169.png

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
    )

 

 


.



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.