Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, I am trying to add a new colum to a table which calculates the number of days between a particular (end) date and today. My problem is that some of the dates in the end date, is before todays date so I get an #ERROR message.
Ideally i'd like
If the End date is > than todays date, return the number of days
If the End Date is < than todays date, return a -number of days
I have tried to use the following
Please can anyone help?? I'm still very new to Power BI and DAX so still finding my feet 🤗
Solved! Go to Solution.
Hi @annalisekerr ,
I have a test tamerj1 's code and I find there is something wrong in it. It will show error : The start date in Calendar function can not be later than the end date.
Update Code:
Workdays between today and end date =
VAR _CheckCondition = 'Iterations (2)'[Correct End Date] > 'Iterations (2)'[Todays Date]
VAR _MinDate =
IF (
_CheckCondition ,
'Iterations (2)'[Todays Date],
'Iterations (2)'[Correct End Date]
)
VAR _MaxDate =
IF (
_CheckCondition ,
'Iterations (2)'[Correct End Date],
'Iterations (2)'[Todays Date]
)
VAR _Result =
COUNTROWS (
FILTER (
ADDCOLUMNS (
CALENDAR ( _MinDate, _MaxDate ),
"Day Of Week", WEEKDAY ( [Date], 2 )
),
NOT ( [Day Of Week] IN { 6, 7 } )
)
)
RETURN
IF (_CheckCondition,_Result,-_Result)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for the correction. I've created the variables but missed to use them 🙂 I will update my reply accordingly
Hi @annalisekerr
One way to that:
Workdays between today and end date =
VAR CheckCondition = 'Iterations (2)'[Correct End Date] > 'Iterations (2)'[Todays Date]
VAR MinDate =
IF (
CheckCondition ,
'Iterations (2)'[Todays Date],
'Iterations (2)'[Correct End Date]
)
VAR MaxDate =
IF (
CheckCondition ,
'Iterations (2)'[Correct End Date],
'Iterations (2)'[Todays Date]
)
VAR Result =
COUNTROWS (
FILTER (
ADDCOLUMNS (
CALENDAR ( MinDate , MaxDate ),
"Day Of Week", WEEKDAY ( [Date], 2 )
),
NOT ( [Day Of Week] IN { 6, 7 } )
)
)
RETURN
IF ( CheckCondition , Result, - Result )
Thank you , I will give this a try
Hi:
Can you try:
Thank you , I will give this a try
Hi @annalisekerr ,
I have a test tamerj1 's code and I find there is something wrong in it. It will show error : The start date in Calendar function can not be later than the end date.
Update Code:
Workdays between today and end date =
VAR _CheckCondition = 'Iterations (2)'[Correct End Date] > 'Iterations (2)'[Todays Date]
VAR _MinDate =
IF (
_CheckCondition ,
'Iterations (2)'[Todays Date],
'Iterations (2)'[Correct End Date]
)
VAR _MaxDate =
IF (
_CheckCondition ,
'Iterations (2)'[Correct End Date],
'Iterations (2)'[Todays Date]
)
VAR _Result =
COUNTROWS (
FILTER (
ADDCOLUMNS (
CALENDAR ( _MinDate, _MaxDate ),
"Day Of Week", WEEKDAY ( [Date], 2 )
),
NOT ( [Day Of Week] IN { 6, 7 } )
)
)
RETURN
IF (_CheckCondition,_Result,-_Result)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Perfect, yes that works brilliantly... thank you
Thank you for the correction. I've created the variables but missed to use them 🙂 I will update my reply accordingly