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

5 REPLIES 5
Memorable Member

Hi,

Yes, its possible, see below:

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

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

Kind regards,
Steve.

Proud to be a Super User!

Frequent Visitor

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!

Memorable Member

@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

Proud to be a 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.

Frequent Visitor

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.

Announcements

#### 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.

#### 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!

#### 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