Forum Discussion

BishwaR's avatar
BishwaR
Icon for Helper V rankHelper V
5 years ago
Solved

Modified Date Slicer

I have two tables Date Table and the Sales Fact Table as given in the pic I have one to many relationship btw them. Now I want to show in the power BI report only yesterday's sales (April 29,2...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi BishwaR ,

     

    First create a column as below:

    _rank = RANKX('Data','Data'[Sales Date],,ASC,Dense)

    Then create a measure as below:

    Measure = 
var _rank=CALCULATE(MAX('Data'[_rank]),FILTER(ALL(Data),'Data'[Sales Date]=SELECTEDVALUE('Date Table'[Date])))
var _tab1=CALCULATETABLE(VALUES('Data'[Sales Date]),FILTER(ALL(Data),'Data'[_rank]=_rank-1))
var _tab2=CALCULATETABLE(VALUES(Data[Sales Date]),FILTER(ALL(Data),'Data'[_rank]>=_rank-3&&'Data'[_rank]<_rank))
Return
IF(SELECTEDVALUE('Date Table'[Day])="Mon",
IF(MAX('Data'[Sales Date]) in _tab2,1,BLANK()),
IF(MAX('Data'[Sales Date]) in _tab1,1,BLANK()))

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

Anonymous's avatar
Anonymous
Not applicable
5 years ago

hi BishwaR - The most ideal solution would be to create a working / business day flag in the Date dimension table - that way you can also handle occurances of holidays etc. so you accurately get the data for the previous working day.

You can use the steps highlighted in the article below:

http://www.rad.pasfu.com/index.php?/archives/166-Script-to-Generate-Date-Dimension-with-Power-Query-M-With-Financial-Columns.html

Do mark / vote the response as a solution if this helps you - this will also help others in the community with a similar question. 

Happy to help!