Filter date range of different tables

[Syndicate_Admin]

Good
I require the help and knowledge of each one of you, I explain what it is, I have 3 tables
Table of users = unique users with dates of entry and exit to the sales department
Sales table = user sales and their respective dates
Calendar table = calculated table

I need to know the number of active users according to the date filter you apply (calendar table) and at the same time the number of unique users who had sales in that same selected date range, such as:

Date range #1
From 1-Jun-22 to 31-Aug-22
active users 25
Users with 5 sales

Date range #2

from 1-Nov-22 to 30-Nov-22
active users 20
Users with sales 0

I have tried several scenarios, calculated tables, filter, selectedvalue, max min and I can not reach you

I attach the example GDPhttps://1drv.ms/u/s!Av56_TS83Z3xgqYqyL5zK8nBpcKVjw?e=z7MB9n

[grandtotal · Accepted solution]

You need to set Calendario as date table.
Set the relationship between Calendario and Ventas:

And use these measures:

usuarios activos = 
VAR minDate = MIN(Calendario[Date])
VAR maxDate = MAX(Calendario[Date])
VAR result =
       COUNTROWS(
              FILTER(
                     Ventas,
                     Ventas[fecha de venta] >= minDate && Ventas[fecha de venta] <= maxDate
              )
       )
RETURN
result

usuarios con ventas = 
VAR minDate = MIN(Calendario[Date])
VAR maxDate = MAX(Calendario[Date])
VAR result =
       COUNTROWS(
              FILTER(
                     Usuarios,
                     'Usuarios'[entrada] >= minDate && 'Usuarios'[Salida] <= maxDate
              )
       )
RETURN
result

I hope this will help!

[Syndicate_Admin]

Thanks for a prompt response, but the proposed solution does not work with the filters, as selected for example in Nov 2022 there are 20 active users and 0 users with sales, I imagine it is by using the ALL function in the FILTER, we try changing it to ALLSELECTED and it did not work, the solution shows the total of the users regardless of the date range that I select
PBI example download

[Analist]

You can use the CALCULATE and DISTINCTCOUNT functions. Here's an example of how you might do this:

Active Users:= CALCULATE ( DISTINCTCOUNT ( users[user_id] ), FILTER ( ALL ( calendar ), calendar[date] >= MIN ( users[start_date] ) && calendar[date] <= MAX ( users[end_date] ) ) )

Users with Sales:= CALCULATE ( DISTINCTCOUNT ( sales[user_id] ), FILTER ( ALL ( calendar ), calendar[date] >= MIN ( sales[date] ) && calendar[date] <= MAX ( sales[date] ) ) )

This will create two measures that you can use to get the number of active users and the number of unique users with sales for a given date range. You can use a filter or slicer based on the calendar[date] column to select the desired date range.

The Active Users measure counts the number of distinct user_id values in the users table for which the calendar[date] is between the start_date and end_date of the user. The Users with Sales measure counts the number of distinct user_id values in the sales table for which the calendar[date] is between the date of the sale.

[Syndicate_Admin]

The proposed solution did not work because of the ALL function, it was tried with ALLSELECTED and continued to show the total number of users.

I have managed to correct it as follows:

in the relational model only exixte a relationship from the calendar table to sales,

a measure with varieble was created, the variable builds a date from a SELETEDVALUE, this will filter the table of the users according to the selected range,
then we created a MAXX which will determine what is the maximum number of active users I had, according to the selected period

Usuarios Activos = 
// Variable crea fecha a partir del valor seleccionado, si la seleccion es multiple toma el max de la seleccion de la tabla calendario

VAR fecha_maxima_seleccionada =
    DATE ( SELECTEDVALUE ( Calendario[Year], YEAR ( MAX ( Calendario[Date] ) ) ), SELECTEDVALUE ( Calendario[Month], MONTH ( MAX ( Calendario[Date] ) ) ), DAY (
        EOMONTH (
            SELECTEDVALUE ( Calendario[Date], DAY ( MAX ( Calendario[Date] ) ) ),
            0
        )
    ) )

RETURN
//calcula la cant de usuarios segun el filtro de la fecha maxima consultada o seleccionada
    
CALCULATE (
        [Cantidad de Usuarios en registro], 
        FILTER (
            Usuarios,
            Usuarios[entrada] < fecha_maxima_seleccionada
                && Usuarios[Salida] > fecha_maxima_seleccionada
        )
    )

Attach the example PBI + the source