Date slicer showing previous day excluding weekends.

[KhrystinaM]

Hello, I have a PowerBI dashboard where I only want to see data from the prior business date. Is there a formula I can write for this?

[v-yohua-msft · Accepted solution]

Hi, @KhrystinaM 

Based on your information, I create a table:

Please following these steps:

1. Create a new column named PreviousData, and use the following DAX expression:

PreviousData =

VAR a = CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'),'Table'[Date]=EARLIER('Table'[Date])-1))

RETURN

IF(WEEKDAY('Table'[Date])=1||WEEKDAY('Table'[Date])-1=7||WEEKDAY('Table'[Date])-1=1,BLANK(),a)

2. Then create a dashboard, put previousData into the dashboard, create a slicer, and put the Date in, when we select the Date, the dashboard will show the information of the previous day, and will not return the weekend.

For example, if I select the date as Monday, he will not display the data for Sunday.

 

If I select a date on Saturday, it will show the value of the previous day, which is Friday.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yohua-msft · Accepted solution]

Hi, @KhrystinaM 

Based on your information, I create a table:

Please following these steps:

1. Create a new column named PreviousData, and use the following DAX expression:

PreviousData =

VAR a = CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'),'Table'[Date]=EARLIER('Table'[Date])-1))

RETURN

IF(WEEKDAY('Table'[Date])=1||WEEKDAY('Table'[Date])-1=7||WEEKDAY('Table'[Date])-1=1,BLANK(),a)

2. Then create a dashboard, put previousData into the dashboard, create a slicer, and put the Date in, when we select the Date, the dashboard will show the information of the previous day, and will not return the weekend.

For example, if I select the date as Monday, he will not display the data for Sunday.

 

If I select a date on Saturday, it will show the value of the previous day, which is Friday.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KhrystinaM]

@v-yohua-msft, thank you for your reply. The calcuate function is throwing an error. 

[v-yohua-msft · Accepted solution]

Hi, @KhrystinaM 
Create a new column in the home page instead of Custom Column in Power Query.

Powe Query uses M and mine is a DAX expression, so it gets an error in Power Query.

Try selecting New column under Modeling and using DAX again.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KhrystinaM]

That worked, thank you!!

[PijushRoy]

Hi @KhrystinaM 

Your message header and body little confusing
You are taking about Slicer data or filter data in Other visual

Can you share details, expected result and screenshot