cancel
Showing results for 
Search instead for 
Did you mean: 
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.

 

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors