Forum Discussion
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 ...
stevedep
Memorable Member
Memorable MemberHi,
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.
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!
- stevedep
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