Forum Discussion

baijumohan1990's avatar
baijumohan1990
Helper II
4 years ago
Solved

Dynamic line chart axis based on the date selection

Hi ,   I am creating a simple sales dashboard with last week & current week sales. The requirement is to have the last week sales and current week sales in seperate line charts. The axis of the las...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi baijumohan1990 ,

    According to your description, you want to create a dynamic DateLw table changed by the Date slicer, but a calculated table/column can't affected by slicers, it's by design. You can go to the Data view of the DateLw table to see, it always stays the same no matter how the slicer changes.

    Here's my solution. 

    1. Modify the DateLw table like below. Don't make relationship between DateLw table and other tables.

    DateLw = CALENDARAUTO()

    2. Modify the LastWkSales measure:

    LastWkSales =
IF (
    MAX ( 'DateLw'[Date] ) >= [LastWkStart]
        && MAX ( 'DateLw'[Date] ) <= [LastWkEnd],
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = MAX ( 'DateLw'[Date] ) )
    ),
    BLANK ()
)

    Get the correct result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Super User
4 years ago

baijumohan1990 , In case you need display a range different then what you selected. You need an independent date table

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

 

 

//Date1 is independent Date table, Date is joined with Table 
new measure =
var _max =maxx(allseleceted(date1),date1[date])
var _min =maxx(allseleceted(date1),date1[date])
var datediff1 = datediff(_min,_max,day)+1
var _maxX = _max-datediff1
var _minX = _min -datediff1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_minX && 'Date'[Date] <=_maxX))