Forum Discussion

BishwaR's avatar
BishwaR
Helper 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!

sayaliredij's avatar
sayaliredij
Solution Sage
5 years ago

Hi,

 

I am not using the rank filter which you have created but I have created a measure that works pretty well with the scenario.

 

SalesAmountYesterday =
var lastdays = IF(FORMAT(TODAY(),"dddd") = "Monday",-3,-1)
RETURN
CALCULATE(SUM(SalesData[Amount]),DATESINPERIOD (
'Date'[Date],
TODAY()-1,
lastdays,
DAY
))
 
Could you please try this and check if this works fine?
 
Best Regards,
Sayali
 
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
BishwaR's avatar
BishwaR
Helper V
5 years ago

Thank you so much for your help. Yes it worked fine. But I have at least 4 more attributes like sales with discount, sales before discount etc as a result I have to create the separate measure for each column. So I was looking for a solution by manipulating the date_table rather than the Fact_Table. 

  • sayaliredij's avatar
    sayaliredij
    Solution Sage
    5 years ago

    In this case, you can create a calculation group with the same settings. 

     

    Just saying. in case you want to have only one filter and not have multiple measures

     

    Thanks,

    sayali

    • BishwaR's avatar
      BishwaR
      Helper V
      5 years ago

      If I create all the measures using this set of calculations will I be able to use other slicers like Product Category, Location etc ?

      • sayaliredij's avatar
        sayaliredij
        Solution Sage
        5 years ago

        Yes I think so 

         

        Thanks,

        sayali