Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
)
.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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?
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.
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
)
.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |