Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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 😅
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |