Forum Discussion

grggmrtn's avatar
grggmrtn
Icon for Post Patron rankPost Patron
6 years ago
Solved

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

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?

Need Help
  • grggmrtn's avatar
    grggmrtn
    6 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

    • grggmrtn's avatar
      grggmrtn
      Icon for Post Patron rankPost Patron
      6 years ago

      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's avatar
        grggmrtn
        Icon for Post Patron rankPost Patron
        6 years ago

        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.