Calculating number of working days when I move the date slicer

[Sean500]

Hi

I have a date column in my table which starts from Jan 1st 2021 to till date. The date column updates dynamically

I wanted to create a measure for number of days and visualize in the card. I will create date slicer, whenever move the dates in the slicer , the number of working date changes?

Is it possible in Power BI? Sorry I am new to Power BI

[Anonymous · Accepted solution]

Hi  @Sean500 ,

I created some data:

Here are the steps you can follow：

Create measure.

Measure =
var _min=MINX(ALLSELECTED('Table'),[Date])
var _max=MAXX(ALLSELECTED('Table'),[Date])
return
COUNTX(
    FILTER(ALL('Table'),
   NOT( WEEKDAY('Table'[Date]) ) in {6,7} && 'Table'[Date] >= _min &&'Table'[Date]<=_max),[Date])

Result:

If you still have holiday-related rest dates, you can express these rest dates in a table, and just add a function to Measure that does not belong to these dates.

Create measure:

Measure2 =
var _min=MINX(ALLSELECTED('Table'),[Date])
var _max=MAXX(ALLSELECTED('Table'),[Date])
var _column=SELECTCOLUMNS('Table2',"1",[Date])
return
COUNTX(
    FILTER(ALL('Table'),
   NOT( WEEKDAY('Table'[Date]) ) in {6,7} && 'Table'[Date] >= _min &&'Table'[Date]<=_max&& NOT('Table'[Date]) in _column ),[Date])

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

[Andras1]

Thank you for this tip. This is somewhat working for me but it isn't removing the holidays. I copied and pasted your measure and just changed the table names. Also, my date table has about 20 columns. Can you help me understand what {6,7} is doing? Many thanks! Andra

PREVIEW

[Andras1]

Hi @Anonymous

I consulted Perplexity and it had me change the bottom part of the measure and it is working now. Thanks again!

Measure3 =

var _min=MINX(ALLSELECTED('Table'),[Date])

var _max=MAXX(ALLSELECTED('Table'),[Date])

var _column=SELECTCOLUMNS('Holidays',"1",[Date])

RETURN

COUNTX(

    FILTER(ALL('Table'),

        WEEKDAY('Table'[Date], 2) <= 5 &&  // Monday = 1, Friday = 5

        'Table'[Date] >= _min &&

        'Table'[Date] <= _max &&

        NOT('Table'[Date] IN _column)

    ),

    [Date]

)

[aj1973]

Hi @Sean500 

Yes it is possible. Here is plenty of thread solution to your issue

https://community.powerbi.com/t5/forums/searchpage/tab/message?advanced=false&allow_punctuation=fals...

And for the Holidays or your date columns that updates dynamicaly you will need to add Parameters