Expression with the Today () function

[Anonymous]

I need to create a measure or column that should use the Today () function.

I have a table with two columns of dates, one that is due date that is of type null and the other that is extension date and can have null values, to that extent I need to show the number of days that are missing or the Number of days that passed the date of the extension when it is null and the date of expiration when the date of extension is null.

I tried to use the function today () but with no success.

Dt. Final	Dt. Prorrogação	à vencer (dias)
30/12/2016		-7
08/10/2017		275
08/09/2015	09/12/2015	-394
08/09/2016	09/05/2016	-242

[Vvelarde]

@Anonymous

Hi, Look this thread, i think would be part of the solution

https://community.powerbi.com/t5/Desktop/How-to-use-DATEDIFF-when-End-Date-is-less-than-Start-Date/t...

[Sean]

@Anonymoussince you're always dealing with TODAY - you just need to check IF there has been an extenstion to determine which date to use in the subtraction (at least that's how I understand your question)

If that is the case you can try this

Column (Days) =
IF (
    ISBLANK ( 'Table'[Dt. Prorrogação] );
    'Table'[Dt. Final] - TODAY ();
    'Table'[Dt. Prorrogação] - TODAY ()
)

EDIT: You may have to change the Data Type of the Column in the Modeling Tab to Whole Number otherwise will show you dates!

[Anonymous · Accepted solution]

@Sean

I created the measure but the following error is occurring:

"A single value for column 'Dt. Extension' in table 'DMEGF_ContractsSupplier' can not be determined." This can happen when a measure refers to a column that contains many values without specifying an aggregation such as min, max, count, or to Get a single result. "

If (ISBLANK (DMEGF_ContractsSupplier);

DMEGF_ContractsProvider [Dt. Final] - TODAY ();

DMEGF_ContractsProvider [Dt. Extension] - TODAY ())

[Sean]

@AnonymousThis is a Calculated Column not a Measure!

[Sean]

@Anonymous

If you want a Measure try this instead

Measure =
IF (
    ISBLANK ( DMEGF_ContractsProvider[Dt. Extension] );
    ( MIN ( DMEGF_ContractsProvider[Dt. Final] ) - TODAY () )
        * 1;
    ( MIN ( DMEGF_ContractsProvider[Dt. Extension] ) - TODAY () )
        * 1
)

Good Luck!