Forum Discussion
Dynamic count between dates. Total customers
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 = DateTable2. 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.
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.
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?