Forum Discussion

hawkeyes12's avatar
hawkeyes12
Frequent Visitor
5 years ago

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: 

 

 

 

ALLSELECTED
Bin
Date
DATEDIFF
DAX
Groups
Matrix Visual
Matrix Visualisation
MAX date
power bi

5 Replies

  • stevedep's avatar
    stevedep
    Memorable Member
    5 years ago

    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. 

    • hawkeyes12's avatar
      hawkeyes12
      Frequent Visitor
      5 years ago

      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's avatar
        stevedep
        Memorable Member
        5 years ago

        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's avatar
    amitchandak
    Super User
    5 years ago

    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.

     

    • hawkeyes12's avatar
      hawkeyes12
      Frequent Visitor
      5 years ago

      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.