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.
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.
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)
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 = 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.
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. 🙂