Format date/time calculation - works until I put it in VAR...

[grggmrtn]

I'm not sure why this works FINE

StartDato = 
FORMAT(
CALCULATE(
        MIN( 'nexus2 Fact_VisiteretTid'[Dato] );
        ALL( 'nexus2 Fact_VisiteretTid' );
        'nexus2 Fact_VisiteretTid'[VisitationId] = EARLIER( 'nexus2 Fact_VisiteretTid'[VisitationId] )
    );"dd-mm-yyyy")

But this gives me a "can't compare type text with integer" error message:

StartDato2 = 
var currentStartDate = 
    FORMAT(
    CALCULATE(
        MIN('nexus2 Fact_VisiteretTid'[Dato]);
        ALL('nexus2 Fact_VisiteretTid');
        'nexus2 Fact_VisiteretTid'[VisitationId] = EARLIER('nexus2 Fact_VisiteretTid'[VisitationId])
    );"dd-mm-yyyy")
RETURN
IF(currentStartDate = 01-01-2015; 'nexus2 Fact_VisiteretTid'[Ikrafttraedelsesdato];currentStartDate)

What I'm trying to do is execute the first code, and if it's the 1st of january 2015, I need to return the value from another date column instead.

Any ideas?

[Anonymous]

@grggmrtn Try adding quotes around the date: “01-01-2015". 01-01-2015 might be getting evaluated as math. 

[grggmrtn]

Almost?

StartDato2 = 
var currentStartDate = 
    FORMAT(
    CALCULATE(
        MIN('nexus2 Fact_VisiteretTid'[Dato]);
        ALL('nexus2 Fact_VisiteretTid');
        'nexus2 Fact_VisiteretTid'[VisitationId] = EARLIER('nexus2 Fact_VisiteretTid'[VisitationId])
    );"dd-mm-yyyy")
RETURN
IF(currentStartDate = "01-01-2015"; 'nexus2 Fact_VisiteretTid'[Ikrafttraedelsesdato];currentStartDate)

now gives me another error message:

"Expressions that yield variant data-type cannot be used to define calculated columns. "

😞

[grggmrtn · Accepted solution]

And... I figured it out:

StartDato2 = 
var currentStartDate = 
    FORMAT(
    CALCULATE(
        MIN('nexus2 Fact_VisiteretTid'[Dato]);
        ALL('nexus2 Fact_VisiteretTid');
        'nexus2 Fact_VisiteretTid'[VisitationId] = EARLIER('nexus2 Fact_VisiteretTid'[VisitationId])
    );"dd-mm-yyyy")
RETURN
IF(currentStartDate = "01-01-2015"; FORMAT('nexus2 Fact_VisiteretTid'[Ikrafttraedelsesdato];"dd-mm-yyyy");currentStartDate)

The result of currentStartDate is text, meaning that the RETURN was setting text with a date [Ikrafttraedelsesdato], so I just needed to format that, and it's working now.