Get count of records that were active on a specific date

[KJanssens]

I want to create a measure to calculate the number of records that were active on a specific date.

I came up with a measure like this:

NumberOfRecordsActiveOnDate = CALCULATE(DISTINCTCOUNT(mytable[ID]), FILTER(mytable, AND(mytable[createdon] <= RELATED(mydatetable[date]), mytable[deletedon] < RELATED(mydatetable[date])))

However, this does not return anything when I filter on a specific date...

any ideas?

thanks,

Koen

[marceloVVR]

Well, if you understand how you need to filter an amount between data.
You will have two possibilities.

1- use the DATESBETWEEN function in filter
Example:

CALCULATE(DISTINCTCOUNT(table[ID]), FILTER(table, DATESBETWEEN(datetable[date],table[minDate],mytable[maxDate])))

link doc: https://docs.microsoft.com/pt-br/dax/datesbetween-function-dax

2- Use the logical operators the same as you were doing, but changing the following locations:

CALCULATE(DISTINCTCOUNT(mytable[ID]), FILTER(mytable, (mydatetable[date] >=mytable[createdon]

&& mydatetable[date]<= mytable[deletedon] )))

test, if it does not solve let know.

[KJanssens · Accepted solution]

I just came accross the following post:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Active-records-over-time-based-on-Criteria/m-...

this will probably lead me in the right direction...

[KJanssens]

@marceloVVR  should I have relations between the datetable and both datefields in my table?

like:

- datetable[date] 1----* mytable[startdate]

- datetable[date] 1----* mytable[enddate]

or doesn't that matter?

[v-lili6-msft]

hi  @KJanssens 

For your case, it is a startdate and enddate problem, Very common. See this post about it:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

you don't need to create create two relationship with date table, just create a measure as this blog and use date field from calendar.

If you still have the problem, please share some sample date and your expected output.

Regards,

Lin

[marceloVVR]

It doesn't matter, being data of the same type and use the FILTER before, as in the example I sent