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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
baijumohan1990
Helper II
Helper II

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 last week sales line chart should be the last week start date & end date of the selected date range.  I created following measures 

Startdate = CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date'[Date] ) )
Enddate= CALCULATE ( MAX( 'Date'[Date] ), ALLSELECTED ( 'Date'[Date] ) )
LastWkStart = [Startdate] - 7
LastWkEnd =   [Enddate]  - 7
LastWkSales = CALCULATE(sum('Table'[Sales]),DATESBETWEEN('Date'[Date],[LastWkStart],[LastWkEnd]))
 
For the last week date range, i created a calender table.
 
DateLw = CALENDAR([LastWkStart],'Table'[LastWkEnd])
 
Data model is looks like below. 
 
baijumohan1990_0-1658931070098.png

 

I created a line chart with DateLw as X axis & LastWkSales  as Y axis.  

baijumohan1990_1-1658931207086.png

 

 This chart is showing Selected Date range as the X axis. The expected output is to show dates between the Last week start & End date as the X axis. For eg :  If the daterange selected is between Jan 9th & Jan 15th , the chart should show the X axis with dates between Jan 2nd & jan 8th 

 
1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

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.

vkalyjmsft_0-1659422840928.png

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.

View solution in original post

4 REPLIES 4
v-yanjiang-msft
Community Support
Community Support

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.

vkalyjmsft_0-1659422840928.png

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.

Thank you. that's works like a charm 🙂

baijumohan1990
Helper II
Helper II

hi @amitchandak    

I tried your solution as suggested 

1. Created an indepdendant date table 

Date_LW = CALENDAR("2021-01-01","2022-12-31")
2. Data model looks like below now
 
 3. Created mesaure LWSales .I changed Maxx to Minx for variable var _min . Thought it was a typo error. 
LWSales =
var _max =maxx(ALLSELECTED(Date_LW),Date_LW[Date])
var _min =MAXX(ALLSELECTED(Date_LW),Date_LW[Date])
var datediff1 = datediff(_min,_max,day)+1
var _maxX = _max-datediff1
var _minX = _min -datediff1
return
calculate( sum('Table'[Sales]), filter('Date', 'Date'[Date] >=_minX && 'Date'[Date] <=_maxX))
 
3. Created the chart as below. But its not giving any output.
 
baijumohan1990_1-1658986508844.png

 

 
 
amitchandak
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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