Forum Discussion

dustdaniel's avatar
dustdaniel
Helper II
1 year ago
Solved

Dynamic count between dates. Total customers

Hi there.

My fact table includes StartDate (FechaSalida) and EndDate (FechaRetorno), I need to count the total clients with an active service based on the selected date (filtered slicer)

 

I have a TableDate in relationship with StartDate.

 

This is a sample

 

This is what I'm trying but this is only filtering those who started on the selected date

Contratos Activos = 
CALCULATE(
    [Total Contratos],
    FILTER(
        Contrato,
        Contrato[FechaSalida] <= SELECTEDVALUE( DateTable[Date] ) &&
        Contrato[FechaRetorno] >= SELECTEDVALUE( DateTable[Date] )
    )
)

 

The expected output is the total active clients by the selected date, that should include those who started the service before the selected date.

 

This is a representation of the total # I'm looking for:

 

Thanks in advanced.

 

Need Help
  • sevenhills's avatar
    sevenhills
    1 year ago

    Could you try these: (Ignore what I said before to use ALL)

     

    1. Create a duplicate table of date as below (you can refine the columns if needed later, once you get the solution) and make sure in the model it is not connected to the transaction table "Contrato".

    Date Table 2 = DateTable

     

    2. Add the date range slicer using Date column for the "Date Table 2"

     

    3. Add this measure into your group

    Contratos Activos 2 = 
VAR _Min_FilterDate = MIN('Date Table 2'[Date]) 
VAR _Max_FilterDate = Max('Date Table 2'[Date]) 
RETURN   
CALCULATE(
    COUNTROWS(Contrato), Contrato[FechaSalida] <= _Max_FilterDate, Contrato[FechaRetorno] >= _Min_FilterDate, REMOVEFILTERS('Date Table 2') 
)

     

    4. Add the table visual ( I added dummy date range visual to show the selected date range, which is not your requirement though)

     

     

     

     

    See if this helps!

     

    Also, if what you are doing is inflight events or events in progress pattern.

    then check this article for more details too: https://www.daxpatterns.com/events-in-progress/

     

     

     

     

    I don't know spanish, have to google and understand the column names meaning. 

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for the replies from sevenhills and Ashish_Mathur.

     

    Hi dustdaniel ,

     

    Did you get your problem solved, based on your description I created simple data and got the expected result, hope this will fulfill your requirement, here is the sample data:

     

     

    1.Create a measure:

     

     

    Measure = 
VAR _max=MAX('date'[Date])
VAR _min=MIN('date'[Date])
RETURN
CALCULATE(
    COUNTROWS('Table'),
    FILTER(ALL('Table'),'Table'[StartDate]>=_min&&'Table'[EndDate]<=_max))

     

     

     

    2.Since you don't want past customer information, create a new measure for filtering:

     

     

    Measure2 = 
VAR _min=MIN('date'[Date])
VAR _max=MAX('date'[Date])
RETURN 
if(MIN('Table'[StartDate])>=_min&&MAX('Table'[EndDate])<=_max,1,0)

     

     

     

    3.The final result is as follows:

    Best Regards,
    Zhu
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

    • dustdaniel's avatar
      dustdaniel
      Helper II
      1 year ago

      Hi Zhu,

       

      I tried what you suggested but I couldn't make it work, would you take a look to the sample I posted recently?

       
  • sevenhills's avatar
    sevenhills
    Super User
    1 year ago

    Try this:

     

     

    Contratos Activos = 
var _paramDt = SELECTEDVALUE( DateTable[Date] ) 

RETURN CALCULATE(
    [Total Contratos],
    FILTER(
        ALLSELECTED(Contrato),
        Contrato[FechaSalida] <= _paramDT && Contrato[FechaRetorno] >= _paramDT
    )
)

     

     ... if not use ALL in place of ALLSELECTED

    • dustdaniel's avatar
      dustdaniel
      Helper II
      1 year ago

      Thankyou sevenhills 

       

      It looks good, I just need to validate if this is correct, but I guess because it includes the ALL funtion in filter, in the matirx on the right is not listing the 859 selected. any thoughts how I could confirm?

       

      Active clients normaly are no longer than 30 days periods, so clientes from 2022 shouldn't be listed 

       

      PD: didn't work with ALLSELECTED, I used ALL instead as you recommended.

       

      Thanks

      • sevenhills's avatar
        sevenhills
        Super User
        1 year ago

        pls. could you share the screenshot of the model linked between tx table and date table; and the formula used for the measure [Total Contratos]