Dax format measure as date

[Laila92]

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. 

[Laila92]

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]
   )

[amitchandak]

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

[Laila92]

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

[az38]

Hi @Laila92 

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

[az38]

Hi @Laila92 

[Laila92]

I dont have that option.

[az38]

@Laila92 

what exactly statement you use for [Days since call] field?

[Laila92]

Days since last Checkin = 
var lastCall = [Last Check-In Call]
var today = FORMAT([Today],"YYYY/MM/DD")
RETURN
 IF([Last Check-In Call]=0,"",DATEDIFF(lastCall,TODAY(),DAY))

Currently this. But i played around with different formatting optinos.

[Laila92]

I dont even have the option to select that.