Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, I have a measure created with DAX to get the VAR of working days between the oldest bulk email date and todays date.
10 E Overdue Days =
VAR STARTDATE = CALCULATE (
MIN ('Emails'[Due_Date__c.1]),
FILTER (ALL ( 'Emails'[Due_Date__c.1]), CALCULATE(COUNT('Emails'[Due_Date__c.1])>10))
)
VAR ENDDATE = TODAY()
RETURN
IF (
STARTDATE < ENDDATE,
COUNTROWS (
FILTER (
ADDCOLUMNS (
CALENDAR ( STARTDATE, ENDDATE ),
"Day of Week", WEEKDAY ( [Date], 2 )
),
[Day of Week] <> 6
&& [Day of Week] <> 7
)
),
COUNTROWS (
FILTER (
ADDCOLUMNS (
CALENDAR ( ENDDATE, STARTDATE ),
"Day of Week", WEEKDAY ( [Date], 2 )
),
[Day of Week] <> 6
&& [Day of Week] <> 7
)
)
)
However I am finding that when the result is retrieved for the same day it still giving a value of 1 rather than 0. What do I need to add to this formula for it to return 0 for the same day?
Here is an example taken on 09/01/2023, ideally the two values for 9/01/2023 would be showing 0:
Solved! Go to Solution.
Hi @job23 ,
When your start date and end date are the same day, Calendar() will still produce a table with only one day, so your result is 1 when they are the same day, if you want the result to be 0 for the same day, try this:
10 E Overdue Days =
VAR STARTDATE =
CALCULATE (
MIN ( 'Emails'[Due_Date__c.1] ),
FILTER (
ALL ( 'Emails'[Due_Date__c.1] ),
CALCULATE ( COUNT ( 'Emails'[Due_Date__c.1] ) > 10 )
)
)
VAR ENDDATE =
TODAY ()
RETURN
SWITCH (
TRUE (),
STARTDATE < ENDDATE,
COUNTROWS (
FILTER (
ADDCOLUMNS (
CALENDAR ( STARTDATE, ENDDATE ),
"Day of Week", WEEKDAY ( [Date], 2 )
),
[Day of Week] <> 6
&& [Day of Week] <> 7
)
),
STARTDATE > ENDDATE,
COUNTROWS (
FILTER (
ADDCOLUMNS (
CALENDAR ( ENDDATE, STARTDATE ),
"Day of Week", WEEKDAY ( [Date], 2 )
),
[Day of Week] <> 6
&& [Day of Week] <> 7
)
),
STARTDATE = ENDDATE, 0
)
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @job23 ,
When your start date and end date are the same day, Calendar() will still produce a table with only one day, so your result is 1 when they are the same day, if you want the result to be 0 for the same day, try this:
10 E Overdue Days =
VAR STARTDATE =
CALCULATE (
MIN ( 'Emails'[Due_Date__c.1] ),
FILTER (
ALL ( 'Emails'[Due_Date__c.1] ),
CALCULATE ( COUNT ( 'Emails'[Due_Date__c.1] ) > 10 )
)
)
VAR ENDDATE =
TODAY ()
RETURN
SWITCH (
TRUE (),
STARTDATE < ENDDATE,
COUNTROWS (
FILTER (
ADDCOLUMNS (
CALENDAR ( STARTDATE, ENDDATE ),
"Day of Week", WEEKDAY ( [Date], 2 )
),
[Day of Week] <> 6
&& [Day of Week] <> 7
)
),
STARTDATE > ENDDATE,
COUNTROWS (
FILTER (
ADDCOLUMNS (
CALENDAR ( ENDDATE, STARTDATE ),
"Day of Week", WEEKDAY ( [Date], 2 )
),
[Day of Week] <> 6
&& [Day of Week] <> 7
)
),
STARTDATE = ENDDATE, 0
)
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
59 | |
22 | |
18 | |
16 | |
15 |
User | Count |
---|---|
85 | |
54 | |
45 | |
39 | |
21 |