How to get remaining days from current date (today date)?

[Anonymous]

Hi,

I need equation for add column to calculate remaining days from current date. The remaining days is between TODAY date to CLOSED DATE.

I already use DATEDIFF but it does't work because CLOSED DATE have date data before and after TODAY date.

[Anonymous]

This is only work if you are refreshing your data at least once a day, but try this:

Days Remaining = 
VAR ClosedDate = [Closed Date]
RETURN

IF(
	ClosedDate > TODAY(),
	DateDiff(Today(), ClosedDate, DAY),
	0
)

[Anonymous]

@Anonymous

What do you mean refreshing once a day? Need to refresh on power BI service and publish again or refresh on power Desktop?

Thanks

[Anonymous]

Calculated columns do their value calculation at the point in time that the refresh of the base data is downloaded.  If you don't refresh once a day, your values in this column will not change.

Typically in the Power BI Service you'll organise the refreshes directly (if on something like OneDrive) or through a gateway.

[Anonymous]

It doest not work for me, its has error below

DAX comparison operations do not support comparing values of type Text with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values.

[Anonymous · Accepted solution]

My apologies, try this instead:

Days Remaining = 
VAR ClosedDate = [Closed Date]
RETURN

SWITCH(
	TRUE,
	DATEVALUE(ClosedDate) < TODAY(), 0,
	DATEVALUE(ClosedDate) >= TODAY(), DateDiff(Today(), DATEVALUE(ClosedDate), DAY)
)

[Anonymous]

Hi, this might be helpful to me also. 

I am looking for the %completion of the year. 

Is the closed date in this example equivalent to the end of the year?

Example: today is April 20, 2020, the measure should display 30.28% (completion of the year).

I need this to be sliced per quarter and month too. 

Thanks in advance!

[Anonymous]

This will give you that percentage:

% Year Complete = var currentDate = TODAY()
var EOY = DATE(YEAR(currentDate), 12, 31)
var SOY = DATE(YEAR(currentDate), 1, 1)
var output = DIVIDE(
    currentDate - SOY,
    EOY - SOY
)
RETURN
output

[Anonymous]

Thanks for your reply. I already got the solution though

%Completion:=

Var Remaining = today()-FIRSTDATE('Calendar'[Date])

Var Totaldays = LASTDATE('Calendar'[Date])-FIRSTDATE('Calendar'[Date])+1

return

if((DIVIDE(Remaining,Totaldays))>1,1,if(divide(Remaining,Totaldays)<0,0,DIVIDE(Remaining,Totaldays)))

[Anonymous]

Hi @Anonymous

Its work now. Thanks for helping me.

Can you explain this coding phares operation?

VAR ClosedDate = [Date Closed Pbi]
RETURN

Thanks

[Anonymous]

Its referred to as a variable.  It places the data into memory meaning the statement doesn't have to keep referring back to the field every time it wants to use the value.