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

The best approach is creating a flag in your date table in power query. 

Add a custom column in power query to your date table called current day flag.  
then add formula

if [date] <= Date.adddays(Date.From(DateTime.LocalNow()) , -1 ) then 1 else 0. 
this should create a column where every date before previous day is 1. 
then add the flag to your power bi filter and select 1 it will show all results. 

if you need help send me the date calendar and I can do it for you. 🙂 

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

Thanks for the help I created the Flag with 1. But I need to select only the latest sales. Like if today is April 30 then in the report I have to show the sales for April 29th only. Now all the previous days are having the Flag value 1 the report is showing all the sales. To handle this I had created a Rank and picking up 1 for yesterday's sales but I need to show Friday.Saturday and Sunday 3 days sales on Monday which I was struggling with.