Working day table (that updates without visual relative date filter)

[nbarjesteh]

Hi!  I have a table that shows the details of Run Time by Machine (No_) for the previous day.  I am using a visual date filter to capture the previous day information.  The problem is that not everyday is a working day and I would love it if I didn't have to keep changing the filter every Monday or after every holiday.  Any thoughts?  I have attached a link to my pbix file.  See "Run Time by No_ (Last Working Day) visual.

Link 

Thanks!

@v-janeyg-msft 

[v-yangliu-msft]

Hi  @nbarjesteh ,

It's not very clear which fields you want in your PBIX file.... If you want to find which are working days, you can find out the holiday table in your area and import it into Power BI Desktop to mark which are off days and which are working day

I created some data:

2022 Holiday：

Calendar table：

Calendar table =

CALENDAR(DATE(2022,1,1),DATE(2022,12,31))

Information：

Here are the steps you can follow：

1. Create calculated column.

IsHoliday =
IF(
    ISBLANK(RELATED('2022 Holiday'[Date]))=FALSE(),
    1,0)

Week = WEEKDAY('Calendar table'[Date],2)

Flag1 =
IF(
    'Calendar table'[IsHoliday]=1||'Calendar table'[Week] in {6,7},1,0)

Index =
RANKX(FILTER('Calendar table',[Flag]=0),[Date],,ASC,Dense)

closest to today =
var _todayIndex=CALCULATE(MAX('Calendar table'[Index]),FILTER(ALL('Calendar table'),'Calendar table'[Date]=TODAY()))
return
CALCULATE(MAX('Calendar table'[Date]),FILTER(ALL('Calendar table'),[Index]<_todayIndex))

2. Create measure.

Flag =
IF(
    MAX('information'[Date])=MAX('Calendar table'[closest to today]),1,0)

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

Best Regards,

Liu Yang

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

[nbarjesteh]

Hi @v-yangliu-msft - Thanks for your response!  Let me see if I can be a little more clear with my desired output.  I came in this moring (Monday) and the table did not show anything, because I have the page filter set to look at a relative date (is in the last: 1 days) and we do not work on the weekends.

So, I had to change my page filter to "(is in the last: 3 days) to capture last Friday (our last working day).

My goal is to have this table show up automatically and not have to update a page date filter.   I have attached the latest pbix here:File Link