Help with excluding week-end

[KatkaS]

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.

[v-robertq-msft · Accepted solution]

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 ))

2. I create a calculated column within the Calendar table:

Day =

var _weekday=WEEKDAY([Date],2)

return

IF(_weekday>=6,"Closed Date" ,"Approved Date")

3. 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.

[PhilipTreacy]

Hi @KatkaS 

Upload the PBIX file to OneDrive or Dropbox then post a link to it in here.

regards

Phil

[PhilipTreacy]

Hi @KatkaS 

What exactly is the issue?  That DAX looks ok.

Can you please provide your data/PBIX file.

Regards

Phil

[KatkaS]

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!