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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
hawkeyes12
Frequent Visitor

Adjust matrix dates (in intervals) based on slicer selection

Hello,

 

I have a Power BI report where I am using the Matrix Visual and have a date slicer.

 

In the matrix, I have the Date value in the Column field. I need the dates to adjust with the dates selected in the slicer and also show in intervals of 7 days. I have tried binning the Date field into 7 days, but the last day shown in the Matrix is related to the latest Sunday, vs the last date selected in the slicer. I have also tried several dax formulas, but I can only get the last date shown in the matrix to correspond with the Maximum date in my data set. 

 

Any ideas on a formula that will allow me to show the Date field in the matrix in increments of 7 days and also display the last date that corresponds with the latest date selected in the slicer? (In my example below, the last date in the matrix should be 8/31 instead of 9/2). Here is the formula I am using to achieve this: 

hawkeyes12_0-1601574312494.png

 

PBI Help.PNG

 

 

5 REPLIES 5
stevedep
Memorable Member
Memorable Member

Hi,

 

Yes, its possible, see below:

dyanmic.gif

Code:

 

___check = 
var _mindate = CALCULATE( MIN(tblSales[sales_date_time]), ALLSELECTED(tblSales))
var _maxdate = CALCULATE(MAX(tblSales[sales_date_time]), ALLSELECTED(tblSales))
var _daysbetween =  SUMX(VALUES(tblSales[sales_ref]), CALCULATE(DATEDIFF( MAX(tblSales[sales_date_time]) , MIN(tblSales[sales_date_time]),DAY)))
var _number = ROUNDDOWN(DATEDIFF(_mindate, _maxdate,DAY) / 7,0)
var _tbl = GENERATESERIES(0,_number,1)
var _tbl2 = SELECTCOLUMNS(ADDCOLUMNS(_tbl, "sales_date_time", _maxdate - ([Value] * 7)), "sales_date_time", [sales_date_time])
return
CALCULATE(SUM(tblSales[sales_amount]),INTERSECT(SELECTCOLUMNS(tblSales,"sales_date_time",[sales_date_time]),_tbl2))

 

Link to file

Please mark as solution if so. Thumbs up for the effort are appreciated.

Kind regards, 
Steve. 

Hi @stevedep thank you so much for the help and detailed reply!! I am trying to apply it to my PBI report but getting stuck on one part: What is the purpose of the Values(tblSales[sales_ref]) part of the bar_daysbetween formula? I can't figure out how to apply it to my scenario.

 

var _daysbetween =  SUMX(VALUES(tblSales[sales_ref]), CALCULATE(DATEDIFF( MAX(tblSales[sales_date_time]) , MIN(tblSales[sales_date_time]),DAY)))

 

Thanks again!

@hawkeyes12 sales ref does not have any purpose, it's some dummy data attribute I added to make it look more realistic. Kind regards Steve

amitchandak
Super User
Super User

@hawkeyes12 , as the header is coming from a column which can be a bin(week end date). A column can not respond to slicer selection. So I doubt that can be done easily.

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Hi @amitchandak thank you for the reply - this information is very helpful! I wasn't sure if there was a way to overcome this with a dax formula, so thank you for letting me know.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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