Modified Date Slicer

[BishwaR]

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

[v-kelly-msft · Accepted solution]

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!

[Sumanth_23]

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

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! 

[Anonymous]

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]

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.

[sayaliredij]

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]

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]

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]

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]

Yes I think so 

Thanks,

sayali