Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
I created a line chart with DateLw as X axis & LastWkSales as Y axis.
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
Solved! Go to Solution.
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.
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.
Thank you. that's works like a charm 🙂
hi @amitchandak
I tried your solution as suggested
1. Created an indepdendant date table
@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))
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
73 | |
72 | |
37 | |
31 | |
26 |
User | Count |
---|---|
95 | |
50 | |
43 | |
40 | |
35 |