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,2021) by default and I achieved it by adding a Rank in the Date Table then in the report filter I select Rank 1 so that dynamically it picks up

yesterday's sales. Here is the pic of report.
Now the problem is how to show Friday's, Saturday's and Sunday's Sales on Monday morning.

As per my current logic only Sunday's sales would be visible in the report. So only on Monday I have to show last three days sales. From Tuesdays through Fridays I have to show yesterday's sales and my current logic works fine in this condition.

 

Thanks

Bishwa

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!

9 Replies

  • 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

  • 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
      Helper 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.

  • 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! 

  • v-kelly-msft's avatar
    v-kelly-msft
    Community 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!