Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
Sean500
Helper I
Helper I

Calculating number of working days when I move the date slicer

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

 

 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @Sean500 ,

I created some data:

vyangliumsft_0-1672811593374.png

 

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:

vyangliumsft_1-1672811593376.png

 

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.

vyangliumsft_2-1672811593376.png

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:

vyangliumsft_3-1672811593377.png

 

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

View solution in original post

4 REPLIES 4
v-yangliu-msft
Community Support
Community Support

Hi  @Sean500 ,

I created some data:

vyangliumsft_0-1672811593374.png

 

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:

vyangliumsft_1-1672811593376.png

 

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.

vyangliumsft_2-1672811593376.png

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:

vyangliumsft_3-1672811593377.png

 

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

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

Hi @v-yangliu-msft

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
Community Champion
Community Champion

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

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Helpful resources

Announcements
Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.

"); $(".slidesjs-pagination" ).prependTo(".pagination_sec"); $(".slidesjs-pagination" ).append("
"); $(".slidesjs-play.slidesjs-navigation").appendTo(".playpause_sec"); $(".slidesjs-stop.slidesjs-navigation").appendTo(".playpause_sec"); $(".slidesjs-pagination" ).append(""); $(".slidesjs-pagination" ).append(""); } catch(e){ } /* End: This code is added by iTalent as part of iTrack COMPL-455 */ $(".slidesjs-previous.slidesjs-navigation").attr('tabindex', '0'); $(".slidesjs-next.slidesjs-navigation").attr('tabindex', '0'); /* start: This code is added by iTalent as part of iTrack 1859082 */ $('.slidesjs-play.slidesjs-navigation').attr('id','playtitle'); $('.slidesjs-stop.slidesjs-navigation').attr('id','stoptitle'); $('.slidesjs-play.slidesjs-navigation').attr('role','tab'); $('.slidesjs-stop.slidesjs-navigation').attr('role','tab'); $('.slidesjs-play.slidesjs-navigation').attr('aria-describedby','tip1'); $('.slidesjs-stop.slidesjs-navigation').attr('aria-describedby','tip2'); /* End: This code is added by iTalent as part of iTrack 1859082 */ }); $(document).ready(function() { if($("#slides .item").length < 2 ) { /* Fixing Single Slide click issue (commented following code)*/ // $(".item").css("left","0px"); $(".item.slidesjs-slide").attr('style', 'left:0px !important'); $(".slidesjs-stop.slidesjs-navigation").trigger('click'); $(".slidesjs-previous").css("display", "none"); $(".slidesjs-next").css("display", "none"); } var items_length = $(".item.slidesjs-slide").length; $(".slidesjs-pagination-item > button").attr("aria-setsize",items_length); $(".slidesjs-next, .slidesjs-pagination-item button").attr("tabindex","-1"); $(".slidesjs-pagination-item button").attr("role", "tab"); $(".slidesjs-previous").attr("tabindex","-1"); $(".slidesjs-next").attr("aria-hidden","true"); $(".slidesjs-previous").attr("aria-hidden","true"); $(".slidesjs-next").attr("aria-label","Next"); $(".slidesjs-previous").attr("aria-label","Previous"); //$(".slidesjs-stop.slidesjs-navigation").attr("role","button"); //$(".slidesjs-play.slidesjs-navigation").attr("role","button"); $(".slidesjs-pagination").attr("role","tablist").attr("aria-busy","true"); $("li.slidesjs-pagination-item").attr("role","list"); $(".item.slidesjs-slide").attr("tabindex","-1"); $(".item.slidesjs-slide").attr("aria-label","item"); /*$(".slidesjs-stop.slidesjs-navigation").on('click', function() { var itemNumber = parseInt($('.slidesjs-pagination-item > a.active').attr('data-slidesjs-item')); $($('.item.slidesjs-slide')[itemNumber]).find('.c-call-to-action').attr('tabindex', '0'); });*/ $(".slidesjs-stop.slidesjs-navigation, .slidesjs-pagination-item > button").on('click keydown', function() { $.each($('.item.slidesjs-slide'),function(i,el){ $(el).find('.c-call-to-action').attr('tabindex', '-1'); }); var itemNumber = parseInt($('.slidesjs-pagination-item > button.active').attr('data-slidesjs-item')); $($('.item.slidesjs-slide')[itemNumber]).find('.c-call-to-action').attr('tabindex', '0'); }); $(".slidesjs-play.slidesjs-navigation").on('click', function() { $.each($('.item.slidesjs-slide'),function(i,el){ $(el).find('.c-call-to-action').attr('tabindex', '-1'); }); }); $(".slidesjs-pagination-item button").keyup(function(e){ var keyCode = e.keyCode || e.which; if (keyCode == 9) { e.preventDefault(); $(".slidesjs-stop.slidesjs-navigation").trigger('click').blur(); $("button.active").focus(); } }); $(".slidesjs-play").on("click",function (event) { if (event.handleObj.type === "click") { $(".slidesjs-stop").focus(); } else if(event.handleObj.type === "keydown"){ if (event.which === 13 && $(event.target).hasClass("slidesjs-play")) { $(".slidesjs-stop").focus(); } } }); $(".slidesjs-stop").on("click",function (event) { if (event.handleObj.type === "click") { $(".slidesjs-play").focus(); } else if(event.handleObj.type === "keydown"){ if (event.which === 13 && $(event.target).hasClass("slidesjs-stop")) { $(".slidesjs-play").focus(); } } }); $(".slidesjs-pagination-item").keydown(function(e){ switch (e.which){ case 37: //left arrow key $(".slidesjs-previous.slidesjs-navigation").trigger('click'); e.preventDefault(); break; case 39: //right arrow key $(".slidesjs-next.slidesjs-navigation").trigger('click'); e.preventDefault(); break; default: return; } $(".slidesjs-pagination-item button.active").focus(); }); }); // Start This code is added by iTalent as part of iTrack 1859082 $(document).ready(function(){ $("#tip1").attr("aria-hidden","true").addClass("hidden"); $("#tip2").attr("aria-hidden","true").addClass("hidden"); $(".slidesjs-stop.slidesjs-navigation, .slidesjs-play.slidesjs-navigation").attr('title', ''); $("a#playtitle").focus(function(){ $("#tip1").attr("aria-hidden","false").removeClass("hidden"); }); $("a#playtitle").mouseover(function(){ $("#tip1").attr("aria-hidden","false").removeClass("hidden"); }); $("a#playtitle").blur(function(){ $("#tip1").attr("aria-hidden","true").addClass("hidden"); }); $("a#playtitle").mouseleave(function(){ $("#tip1").attr("aria-hidden","true").addClass("hidden"); }); $("a#play").keydown(function(ev){ if (ev.which ==27) { $("#tip1").attr("aria-hidden","true").addClass("hidden"); ev.preventDefault(); return false; } }); $("a#stoptitle").focus(function(){ $("#tip2").attr("aria-hidden","false").removeClass("hidden"); }); $("a#stoptitle").mouseover(function(){ $("#tip2").attr("aria-hidden","false").removeClass("hidden"); }); $("a#stoptitle").blur(function(){ $("#tip2").attr("aria-hidden","true").addClass("hidden"); }); $("a#stoptitle").mouseleave(function(){ $("#tip2").attr("aria-hidden","true").addClass("hidden"); }); $("a#stoptitle").keydown(function(ev){ if (ev.which ==27) { $("#tip2").attr("aria-hidden","true").addClass("hidden"); ev.preventDefault(); return false; } }); }); // End This code is added by iTalent as part of iTrack 1859082
Top Solution Authors