Dynamically Calculating Column Max

[pmcewan]

I'm trying to build a direct query report of user activity where the data is retrieved from an MS SQL view.  The view does some calculations to determine if the user is active (not just whether they've logged in or not), and the report needs to have a less than date slicer that when a date is selected, it needs to calculate the MAX active user activity date for each user that is less than or equal to the date slicer.  In other words, based on the active view column, it needs to find the MAX activity date for a user that is active and less than the date slicer.  Then it needs to display the user and MAX date in a datatable.  Does anybody have suggestions on how to make this work?  Because I can't seem to get the MAX dates to recalculate properly based on the slicer date selected

[v-juanli-msft]

Hi @pmcewan 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[v-juanli-msft]

Hi @pmcewan 

It is better to share an example data here and tell me how to detemine whether the user is active.

I made a test as below:

Assume you have data as below,

then you create a date table and create relationships as below

date Table = CALENDARAUTO()

also assume the rule which is active:

for example, for user a, he log in at 2019/4/3, and log out at 2019/4/15,

he is active during 2019/4/3~2019/4/14, so the max date he is active for this period is 2019/4/14.

If all above is the same as your scenario, you could create such a measure

max date active = CALCULATE(MAX(Sheet3[date]),FILTER(ALLEXCEPT(Sheet3,Sheet3[user]),Sheet3[status]="out"))-1

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.