Last Seen Visitor formula

[Anonymous]

Hello Community,

I am trying to detect last seen visitors of my message App, what I do is I take the latest date of my table and substract the latest date the action "REGISTER_LETTER" (send message) is recorded.

I do get the right results with a nice formatting(5 Day, 1 Day etc....) but I have an issue: 
Visitors that never sent a message (0 "REGISTER_LETTER") have a value of 43000, I understand what happens as I substract latest date by BLANK but I don't understand how I can get around this issue... 

If someone have a solution I'd be grateful 🙂 

Here is the formula

Last Seen =
FORMAT(
               CALCULATE(  MAX('Global table'[date])
                                   )
-
               CALCULATE(
                    Max('Global table'[date]),
                    FILTER('Global table','Global table'[event_type]="REGISTER_LETTER")
                                   )
                    *1+1,
                 "#")
& " Day"

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Please change your DAX to the following:

Last Seen= VAR LASTDATEOFREGISTER=CALCULATE(
                    Max('Global table'[date]),
                    FILTER('Global table','Global table'[event_type]="REGISTER_LETTER")
                                   )
                                   RETURN
                                   IF(ISBLANK(LASTDATEOFREGISTER),BLANK(),
FORMAT(
               CALCULATE(  MAX('Global table'[date])
                                   )
-
               LASTDATEOFREGISTER
                    *1+1,
                 "#")
& " Day")

Regards,
Lydia

[Anonymous]

I still have not found any solutions 😞 if someone can help me that'd be great,

Please tell me if my post is unclear and I should edit it!!