Forum Discussion

mailwin33's avatar
mailwin33
Frequent Visitor
9 years ago

Calculate maximum time with grouping earlier rows

Hello, everyone! I stucked with writing a DAX formula that I can't manage to during the week. I have a table that contains data about guests visited our building (with electronic cards. Also we hav...
Need Help
Sean's avatar
Sean
Icon for Community Champion rankCommunity Champion
9 years ago

mailwin33 Try this as a COLUMN - no MAX and no VALUES

 

LastExitTime COLUMN = 
VAR ExitTime = BuildingVisits[BuildingExitTime]
RETURN
    CALCULATE (
        MAX ( BuildingVisits[BuildingExitTime] ),
        FILTER (
            ALLEXCEPT ( BuildingVisits, BuildingVisits[BuildingID], BuildingVisits[EnterDate] ),
            BuildingVisits[BuildingEnterTime] < ExitTime
                && MAX ( BuildingVisits[EnterDate] ) = TODAY ()
        )
    )

 

If you want this calculated only for TODAY( ) try this as a COLUMN as well

 

LastExitTime COLUMN 2 = 
IF (
    BuildingVisits[EnterDate] = TODAY (),
    VAR ExitTime = BuildingVisits[BuildingExitTime]
    RETURN
        CALCULATE (
            MAX ( BuildingVisits[BuildingExitTime] ),
            FILTER (
                ALLEXCEPT ( BuildingVisits, BuildingVisits[BuildingID] ),
                BuildingVisits[BuildingEnterTime] < ExitTime
            )
        )
)

 

mailwin33's avatar
mailwin33
Frequent Visitor
9 years ago

Sean

 

Thank you for the suggestion.

I believe it should works, but in my case it stucks on "Working on it" (both formulas), likely because of lot of rows, trying to loop throuh all of them, regardless I set a filter on EnterDate and use TODAY() in formula

 

Vvelarde

 

Ok, thank you.

I'll think about how to anonimize my data and share it.