Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
So i have a calendar table that looks like this :
Then in other table i have 2 dates between i need to calculate business day diff.
I'm using following formula :
Yet, my __Days_Diff column only get 1's and few occasional 0's, which is obviously wrong
Can any1 advise what I'm doing wrong here ?
Best regards.
@burnt_fingers - See if this helps: https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/td-p/367362
Also, I see you are a new member, Please check out this article: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
@Greg_Deckler Thx for lots of usefull materials.
Unfortunatly your solution does not work either, returns just '1' on each row.
So my sample data looks like this
Custom_Creation Custom_Corretion
4/16/2020 4/16/2020
4/14/2020 4/14/2020
7/9/2020 7/9/2020
7/8/2020 7/9/2020
7/1/2020 7/2/2020
I would like to get difference in working days calculated between those two.
As you can read above, that formula is almost direct copy paste from this forum.
What other requirements are there to make it work ?
I do have active relationship between my __Calendar table and my RAW_Errors Table.
Below is my __Calendar Table.
Date Year Month Day of the Week Working Day Date_Formatted Date_Formatted_2
1/1/2020 12:00:00 AM 2020 1 4 1 01/01/2020 Wednesday, January 1, 2020
1/2/2020 12:00:00 AM 2020 1 5 1 01/02/2020 Thursday, January 2, 2020
1/3/2020 12:00:00 AM 2020 1 6 1 01/03/2020 Friday, January 3, 2020
1/4/2020 12:00:00 AM 2020 1 7 0 01/04/2020 Saturday, January 4, 2020
1/5/2020 12:00:00 AM 2020 1 1 0 01/05/2020 Sunday, January 5, 2020
1/6/2020 12:00:00 AM 2020 1 2 1 01/06/2020 Monday, January 6, 2020
1/7/2020 12:00:00 AM 2020 1 3 1 01/07/2020 Tuesday, January 7, 2020
1/8/2020 12:00:00 AM 2020 1 4 1 01/08/2020 Wednesday, January 8, 2020
1/9/2020 12:00:00 AM 2020 1 5 1 01/09/2020 Thursday, January 9, 2020
1/10/2020 12:00:00 AM 2020 1 6 1 01/10/2020 Friday, January 10, 2020
1/11/2020 12:00:00 AM 2020 1 7 0 01/11/2020 Saturday, January 11, 2020
1/12/2020 12:00:00 AM 2020 1 1 0 01/12/2020 Sunday, January 12, 2020
Hi @burnt_fingers ,
Create a measure as below:
Measure = CALCULATE(SUM('Calendar table'[Working day]),FILTER('Calendar table','Calendar table'[Date]>MAX('Error'[Custom_Creation])&&'Calendar table'[Date]<=MAX('Error'[Custom_Corretion])))+0
And you will see:
If you wanna the same dates return 1 instead of 0,then modify the measure as below:
Measure = CALCULATE(SUM('Calendar table'[Working day]),FILTER('Calendar table','Calendar table'[Date]>=MAX('Error'[Custom_Creation])&&'Calendar table'[Date]<=MAX('Error'[Custom_Corretion])))+0
For details,pls see attached.
Hi,
Thanks for your inputs but i was able to get my results slightly reworking @Greg_Deckler foruma.
It looks like this now :
DaysDiff =
Var Calendar1 = CALENDAR(DURATION_ERRORS[Creation],IF(ISBLANK(DURATION_ERRORS[Correction]),DURATION_ERRORS[Creation],DURATION_ERRORS[Correction]))
VAR Calendar2 = ADDCOLUMNS(
Calendar1,"wkd",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[wkd]<6),[Date])-1
yet im still wondering why the original formula did not work for me.
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |