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!

v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
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!