Forum Discussion
Solved
Return Date based on Conditions from same table
Hi, i've been trying to follow this: Solved: Date difference between values in same column - Microsoft Fabric Community
Since I want to get a similar result. I need to calculate the difference in days from the BaselineDate to Forecast Date for each unique ID to determine if items are running on schedule (The dataset is incomplete - not all IDs have dates set) . I have the following [Master] table:
with the calculated column:
DiffBaselineForcast_days =
VAR _ID = Master[UniqueID]
VAR temp =
TOPN (
1,
FILTER (
Master,
Master[UniqueID] = _ID
&& Master[EntryType] = "ForecastDate"
),
[Date], DESC
)
RETURN
if(Master[EntryType] = "BaselineDate",DATEDIFF(MAXX(temp,[Date]),[Date],DAY),0)
Which seems to be working however can someone explain it to me as I'm struggling to understand how it is working
I'm confused with the MAXX part and that when i try and return temp[Date] it says its not a table?
or suggest a simpler way of calculating this??
Thanks!
After going away and coming back to it I've decided a new approach which I think works better:
Late2 =VAR varCurrentScenarioID = Master[UniqueID]VAR varCurrentEntryType = Master[EntryType]VAR varCurrentDate = Master[Date]VAR DateDifference =IF(varCurrentEntryType = "ForecastDate",DATEDIFF(varCurrentDate,LOOKUPVALUE(Master[Date],Master[UniqueID],varCurrentScenarioID,Master[EntryType],"BaselineDate"),DAY),blank())VAR Result =IF(DateDifference < 0, "Late",BLANK())RETURNResult
1 Reply
After going away and coming back to it I've decided a new approach which I think works better:
Late2 =VAR varCurrentScenarioID = Master[UniqueID]VAR varCurrentEntryType = Master[EntryType]VAR varCurrentDate = Master[Date]VAR DateDifference =IF(varCurrentEntryType = "ForecastDate",DATEDIFF(varCurrentDate,LOOKUPVALUE(Master[Date],Master[UniqueID],varCurrentScenarioID,Master[EntryType],"BaselineDate"),DAY),blank())VAR Result =IF(DateDifference < 0, "Late",BLANK())RETURNResult