Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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]
)
@Laila92 , somehow to date diff in yellow seems fine to me, First date is smaller 22-May to 27 May is 5 days
right. but all the non yellow ones are not fine.
I dont have that option.
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.
I dont even have the option to select that.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |