Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Dax format measure as date

I have a measure that finds the last date a certain action was done per client (before today). The measure does return the right date but I am not able to format it as a date. I need the measure to be an actual date because I want to be able to find the date diff between the measure date and TODAY().

My Measure:

 

Last  Call = 
VAR lastCheckIn = IF(CALCULATE(MAX(Activities[Due date]), 
    FILTER(ALLEXCEPT(Deals, Deals[Org Name]), Deals[Activities.Type] = "Check-in Call")
    )<>BLANK(),CALCULATE(MAX(Activities[Due date]), 
    FILTER(ALLEXCEPT(Deals, Deals[Org Name]), Deals[Activities.Type] = "Check-in Call"),
    USERELATIONSHIP(Dates[Date],Activities[Due date]),
    USERELATIONSHIP(Deals[Org Name],Activities[Org Name])


    ),"")
RETURN  

VAR oneBeforeLastCheckIn = IF(CALCULATE(MAX(Activities[Due date]), 
    FILTER(ALLEXCEPT(Deals, Deals[Org Name]), Deals[Activities.Type] = "Check-in Call")
    )<>BLANK(),CALCULATE(MAX(Activities[Due date]), 
    FILTER(ALLEXCEPT(Deals, Deals[Org Name]), Deals[Activities.Type] = "Check-in Call"),
    FILTER(Activities,Activities[Due date]<TODAY()),
    USERELATIONSHIP(Dates[Date],Activities[Due date]),
    USERELATIONSHIP(Deals[Org Name],Activities[Org Name])

    ),"")
  

return

VAR correctDate = IF(lastCheckIn>TODAY(),oneBeforeLastCheckIn,lastCheckIn)
RETURN

IF ( correctDate = BLANK(), 0, FORMAT(correctDate+ DATE ( 1899, 12, 30 ),"DD/MM/yyyy"))

 


All related date columns are formatted as date. Example of how my output does not work with DATEDIFF(). Strangely, many of the DATEDIFFs do work, see the yellow marked rows, but for some rows the outcome does not make sense. I just guessed this is because my Last Check in measure is not a date. (the "days since call" is a datediff between the above measure and today()). I tried formatting both like:

 

 FORMAT([Today],"YYYY/MM/DD")

 

but this did not work. 



9 Replies

  • Anonymous , somehow to date diff in yellow seems fine to me, First date is smaller 22-May to 27 May is 5 days

    • Anonymous's avatar
      Anonymous
      Not applicable

      right. but all the non yellow ones are not fine.

      • az38's avatar
        az38
        Icon for Community Champion rankCommunity Champion

        Hi Anonymous 

        Check you have no Aggregation in [Days since] column in Visualizations Pane

  • Anonymous's avatar
    Anonymous
    Not applicable

    I actually found a new way to calculate my original measure that preserved the date, and that also fixed the DATEDIFF metric. this is the new measure:

    Last Call = 
    MAXX(
        SUMMARIZE(
            Deals,
            Activities_Lookup[Org Name],
            "Last call", CALCULATE(MAX(Activities_Lookup[Due date]),
                                FILTER(Activities_Lookup,Activities_Lookup[Type]="Check-In Call"),
                                FILTER(Activities_Lookup,Activities_Lookup[Done]="Done"),
                                FILTER(Activities_Lookup,Activities_Lookup[Due date]<=TODAY())
                            )
                ),
       [Last call]
       )