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 ...
  • 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.