Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
dustdaniel
Helper II
Helper II

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

dustdaniel_0-1726259413880.png

 

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] )
    )
)

dustdaniel_1-1726259685634.png

 

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:

dustdaniel_2-1726260926441.png

 

Thanks in advanced.

 

1 ACCEPTED SOLUTION

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

sevenhills_1-1726776074923.png

 

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)

 

sevenhills_2-1726776238331.png

 

 

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. 

View solution in original post

12 REPLIES 12
Anonymous
Not applicable

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:

 

vlinhuizhmsft_0-1726468330561.png

 

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)

 

 

 

vlinhuizhmsft_1-1726468683193.png

3.The final result is as follows:

vlinhuizhmsft_2-1726468738983.png

vlinhuizhmsft_3-1726468825179.png

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.

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?

 
Ashish_Mathur
Super User
Super User

Hi,

I have solved a similar question in the attached file.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
sevenhills
Super User
Super User

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

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.

dustdaniel_0-1726265526810.png

 

Thanks

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]

 

Thank you Sevenhills,

 

I tried what yo suggested but it's not complettly accurate the result, I managed to duplicate the fact table so I would not have a relationship with the Calendar table (I didn't use the original since it will affect may other measures).

 

If I do it without relationship, it doens´t count the rows.

 

dustdaniel_0-1726760313681.pngdustdaniel_1-1726760489410.png

 

If I use the relationship, It does count the rows but there are some erros in the result, like showing active clients in future dates (it´s not corrrect)

dustdaniel_2-1726761454562.png 

dustdaniel_3-1726761543413.png

 

Please let me know what could I do?

 

I created a sample file by removing all sensitive data File_Here 

 

Sorry if my earlier response is not clear.

 

It is the calendar table that needs a copy and not fact table. And use this calendar date table for slicers. Hope it clarifies.

 

Meanwhile, I am looking into your .pbix file and see how I can fix it ... 

 

 

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

sevenhills_1-1726776074923.png

 

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)

 

sevenhills_2-1726776238331.png

 

 

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. 

Thank you Sevenhills!!

I was able to validate the numbers and the count is correct!

Glad to hear that it worked! 

 

Tip: For the Date Table 2, if you are planning to use only for the slicer, then you can remove all other columns. Reason: As the grain of the date table is date and being maintained and other columns are unwanted.

Nostalgia: Mosha was one of the key members of MDX, SSAS world during 2002 - 2013. he used to call this as inflight events when he was at Microsoft. Later he left to Google and then different company. That is when I learnt this concept. 🙂

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.