topic Re: Adjust matrix dates (in intervals) based on slicer selection in DAX Commands and Tips

Adjust matrix dates (in intervals) based on slicer selection

hawkeyes12 — Thu, 01 Oct 2020 17:47:25 GMT

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:

Re: Adjust matrix dates (in intervals) based on slicer selection

amitchandak — Fri, 02 Oct 2020 04:02:19 GMT

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.

Re: Adjust matrix dates (in intervals) based on slicer selection

hawkeyes12 — Mon, 05 Oct 2020 15:46:41 GMT

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.

Re: Adjust matrix dates (in intervals) based on slicer selection

stevedep — Mon, 05 Oct 2020 17:05:30 GMT

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

Link to file.

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

Kind regards,
Steve.

Re: Adjust matrix dates (in intervals) based on slicer selection

hawkeyes12 — Wed, 07 Oct 2020 01:29:04 GMT

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!

Re: Adjust matrix dates (in intervals) based on slicer selection

stevedep — Wed, 07 Oct 2020 04:51:01 GMT

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