Forum Discussion

KatkaS's avatar
KatkaS
Post Patron
5 years ago
Solved

Help with excluding week-end

Hello

 

I have a problem with exlcuding week-ends again... There are lots of suggestions on inyternet and I tried many of them, but still getting error.. I created a separate date table and I would like to calculate difference between Closed date and Approved date..

 

Closed in days = CALCULATE (COUNTROWS ('CALENDAR'),FILTER ('CALENDAR', WEEKDAY ('CALENDAR'[Date], 2 ) < 6 ),DATESBETWEEN ('CALENDAR'[Date],'Report'[Closed Date],'Report'[Approved Date])).
 
Could someone help please?
Thank you.
Need Help
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    Hi, KatkaS 

    According to your description and DAX formula, I guess that you want to get the difference between the count of dates between Approved Date and Closed Date in a period of dates, right?

    If so, you can try my steps:

    1. I create a Calendar table that contains all the dates in 2021:
    Calendar = CALENDAR(DATE(2021,1,1),DATE(2021,12,31 ))
    1. I create a calculated column within the Calendar table:
    Day =

var _weekday=WEEKDAY([Date],2)

return

IF(_weekday>=6,"Closed Date" ,"Approved Date")
    1. I created a measure:
    Difference =

var _approved=COUNTX(FILTER(ALL('Calendar'),[Day]="Approved Date"),[Date])

var _closed= COUNTX(FILTER(ALL('Calendar'),[Day]="Closed Date"),[Date])

return

_approved-_closed

    Then I create a card chart an place this measure, I can get the correct count, like this:

     

    I guess this can be what you want.

    You can download my test pbix file here

     

    If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

    How to Get Your Question Answered Quickly 

     

    Best Regards,

    Community Support Team _Robert Qin

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

4 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    Hi KatkaS 

    What exactly is the issue?  That DAX looks ok.

    Can you please provide your data/PBIX file.

    Regards

    Phil

    • KatkaS's avatar
      KatkaS
      Post Patron
      5 years ago

      Hello Philip,

      it seems the date format was wrong, not the DAX... but now I have an issue with null in the date field..

      Can I send the dashboard to you..? How? Thank you very much!

  • v-robertq-msft's avatar
    v-robertq-msft
    Community Support
    5 years ago

    Hi, KatkaS 

    According to your description and DAX formula, I guess that you want to get the difference between the count of dates between Approved Date and Closed Date in a period of dates, right?

    If so, you can try my steps:

    1. I create a Calendar table that contains all the dates in 2021:
    Calendar = CALENDAR(DATE(2021,1,1),DATE(2021,12,31 ))
    1. I create a calculated column within the Calendar table:
    Day =

var _weekday=WEEKDAY([Date],2)

return

IF(_weekday>=6,"Closed Date" ,"Approved Date")
    1. I created a measure:
    Difference =

var _approved=COUNTX(FILTER(ALL('Calendar'),[Day]="Approved Date"),[Date])

var _closed= COUNTX(FILTER(ALL('Calendar'),[Day]="Closed Date"),[Date])

return

_approved-_closed

    Then I create a card chart an place this measure, I can get the correct count, like this:

     

    I guess this can be what you want.

    You can download my test pbix file here

     

    If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

    How to Get Your Question Answered Quickly 

     

    Best Regards,

    Community Support Team _Robert Qin

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