Calculate how many items are between two dates

[brouwno]

Hi community,

Hope someone can help me with this. I cannot seem to get the right formula.

I have two tables:

- 1st tabel contains a column with a datetime (creationDate) and a column with a locationname (name)

- 2nd table contains two columns with a date time (startDate & endDate) and a column with a locationname (name)

I want to create a matrix where I show:

- Rows: Locations (from table 1)

- Columns: Month (the matrix is filtered by year, i.e. 2020)

- Values: Here I want to show how many rows from the 2nd table, from which the startDate & endDate are in that month of the Matrix.

For example if a record in table 2 had a startDate of 02-11-2019 and an endDate of 15-02-2020, it had to be counted in the maxtrix value for januari and febrari of 2020, but not for march 2020.

I hope I explained correctly, if not: Let me know!

I appreciate all the help you can give me.

[reynaldo_malave · Accepted solution]

HI @brouwno,

This looks like an event in progess problem (not really sure). Take a look  at this aritcle https://www.daxpatterns.com/events-in-progress/ Hope it helps.

Reynaldo

[brouwno]

Thanks for taking the time to give me an reply @reynaldo_malave!!

The link you provided showed a more elegant solution than I had come up with 🙂

It alo turned out that my data sources do not always have alle the information I expected. Therefore the results of my measures were not what I expected. At first I thought my measures were not correct, but it turned out the be a little of both 😅