Date depending on filters

[Anonymous]

Dear all,

I hope you are well.

I have three different tables:

- A calendar table:

- A user table where one row contains all the informations about an user, with the expiry date of its license.

- A visitors table with the logins, one row represents one login.

I need to generalize the below formula so that it generalizes for all dates selected in a slicer and not just the last 30 days:

var group_by_prev_30_days = SUMMARIZE(FILTER('Visitors', Visitors[Date] > MAX('calendar'[Date]) - 30),
Visitors[email]) RETURN CALCULATE(COUNTROWS(users), users[licenseExpiresAt] > MAX('calendar'[Date]) && NOT(users[email]) IN DISTINCT(group_by_prev_30_days))

This formula calculates the number of inactive users.

Thank you in advance for your help! I appreciate it.

A

[Anonymous · Accepted solution]

Thank you all for your help!

I found the solution. Here it is:

- I created a min and a max in the Calendar table that way:

Min_date = CALCULATE(MIN('calendar'[Date]), ALLSELECTED('calendar'[Date]))

Max_date = CALCULATE(MAX('calendar'[Date]), ALLSELECTED('calendar'[Date]))

- Then, I used this formula to create a measure in my Users table:

Distinct_inactive_users =

var group_by_timeperiod = SUMMARIZE(FILTER('Visitors', Visitors[Date] >= [Min_date] && Visitors[Date] <= [Max_date]),

Visitors[Visitor ID])

RETURN COUNTROWS(FILTER(Users, Users[licenseExpiresAt] >= [Max_date] && NOT(Users[id]) IN DISTINCT(group_by_timeperiod) && Users[Account_Name] <> "XXX"))

[Anonymous]

Thank you all for your help!

I found the solution. Here it is:

- I created a min and a max in the Calendar table that way:

Min_date = CALCULATE(MIN('calendar'[Date]), ALLSELECTED('calendar'[Date]))

Max_date = CALCULATE(MAX('calendar'[Date]), ALLSELECTED('calendar'[Date]))

- Then, I used this formula to create a measure in my Users table:

Distinct_inactive_users =

var group_by_timeperiod = SUMMARIZE(FILTER('Visitors', Visitors[Date] >= [Min_date] && Visitors[Date] <= [Max_date]),

Visitors[Visitor ID])

RETURN COUNTROWS(FILTER(Users, Users[licenseExpiresAt] >= [Max_date] && NOT(Users[id]) IN DISTINCT(group_by_timeperiod) && Users[Account_Name] <> "XXX"))

[Anonymous]

Hi @Anonymous ,

Please update the formula of your measure as below and check whether it can get your desired result...

Measure =
VAR _seldate =
SELECTEDVALUE ( 'calendar'[Date] )
VAR _pastdays = 30
VAR _tab =
CALCULATETABLE (
VALUES ( Visitors[email] ),
FILTER (
'Visitors',
'Visitors'[Date] >= MIN ( 'calendar'[Date] )
&& 'Visitors'[Date] <= MAX ( 'calendar'[Date] )
)
)
RETURN
CALCULATE (
DISTINCTCOUNT ( 'users'[email] ),
FILTER (
'users',
'users'[licenseExpiresAt] > _seldate
&& NOT ( 'users'[email] ) IN _tab
)
)

And you can refer the following threads to get the number of inactive users:

Active Employees per Period

Get active employee count based on start and end date range

If the above ones can't help you get the desired result, please provide some sample data in the table visitors and users(exclude sensitive data) and your expected result with backend logic and special examples. Thank you.

Best Regards

[Anonymous]

Dear Whitewater100,

I hope you had a nice weekend.

Thank you for your detailed answer!

If I am not mistaken, it is very close to what I need but not exactly that, I need to be able to do that automatically with the existing date column of the calendar table. The slicer/filter would be the calendar date. Would you know how to do that?

Thank you in advance! I appreciate it!

Take care

A

[Whitewater100]

Hi:

Do you mean:

Determine how many days you want to be used in your measure,based on a selected date. For this you can first do:

SV = SELECTEDVALUE(Dates[Date])

Then: 

Date Diff =

Var vselecteddate = [SV]

return

DATEDIFF(vselecteddate, TODAY(),DAY)

This will count days and this measure can go in your measure instead of -30  you could put "- DateDiff"

Here's a screenshot example: Where the date selected in 1-05-2020 compared to Today. Then you can put this measure (DateDiff) in your original measure to have various days to calculate form.

[Anonymous]

I apologize for being unclear. I want to change the below formula so that I get the number of visitors for the last X days depending on the date column from my calendar table and not on a number of days slicer.

var group_by_prev_30_days =

SUMMARIZE(FILTER('Visitors', Visitors[Date] > MAX('calendar'[Date]) - 30),
Visitors[email]) RETURN CALCULATE(COUNTROWS(users), users[licenseExpiresAt] > MAX('calendar'[Date]) && NOT(users[email]) IN DISTINCT(group_by_prev_30_days))

Is it possible to do it?

Thank you again for your help!

[Whitewater100]

Hi:

Are you saying you would like a calculated column (active users) in your Date Table and then you want to see that figure as it flucuates based on a Date slicer?

If you have a sample of the mdel/data the answer will be better for you. I'm not certain how the email piece works into the calculation.

Thanks..

[charles_g]

Hi araignee,

If I understand correctly, your data model should look something like this :

And here's what I came up with : 

VAR max_date = MAX(Calendar[Date])
VAR nb_days = 30
VAR period = DATESINPERIOD(Calendar[Date], max_date, -nb_days, DAY)

// Users who visited in the selected period
VAR active_users =
CALCULATETABLE(
    Users,
    period,
    CROSSFILTER( Visitors[User ID], Users[User ID], Both )
)

// Users whose license is still valid
VAR valid_users = 
FILTER(
     Users,
     Users[Expiry date] > MAX('Calendar'[Date])
)

// valid users who didn't visit in the selected period
VAR inactive_users = EXCEPT( valid_users, active_users)

RETURN
COUNTROWS(inactive_users)

 There may be a simpler way to do it, but this seems to work.

[Whitewater100]

I may have answered to fast.

You can make a table with GENERATESERIES = see image

After this you can have measure for SELECTEDVALUE('Table Name'[Series column])

Then this can be your variable in your  formula and you point to this table selection in your formula.

1. Create table with GENERATESERIES  eg = GENERATESERIES( 5, 365,5) this means make a table from 5 to 365 by increments of 5.

2. Note the column value for days.

3. Use this column for your slicer

4. Make SELECTEDVALUE variable for your formula 

5. where it says - 30 you can use - SV and it will pick up the selection on your slicer.

If you have some sample data I will show you.

Thanks..

[Whitewater100]

Hi:

You can make a date slicer.

Then create measure such as:

SelectedDate = SELECTEDVALUE('Date'[Date])

Then in your measure above define a variable:

var  vdateselct = SelectedDate   or you can just write the measure in your variable. = SELECTEDVALUE('Date'[Date])

In your formula you substitute this variable in, so instead of "- 30"  yours would say " - vdateselct" 

Hope this works for you!