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.
Hello
I've been struggling with this problem now for couple days.
I've got 2 tables. Data and calendar. Here's an example of data table (it has only 3 columns)
ID Creation date closing date
1 1/1/2000 5/1/2000
2 2/1/2000 6/1/2000
3 2/1/2000 6/1/2000
4 3/1/2000 13/1/2000
5 10/1/2000 14/1/2000
and so on. I have data for about 2 years and now I would like to create a measure that could show day to day the amount of ID's (count) that have been open for 2 or more days. I'd like to calculate so that creation day is 0 and the closing day doesn't count anymore.
So the result matrix would look like this: (date from date table)
Date count
1/1/2000 0
2/1/2000 0
3/1/2000 1 (ID 1)
4/1/2000 3 (ID 1,2,3)
5/1/2000 3 (ID 2,3,4)
6/1/2000 1 (ID 4)
And yes 0 is better value than blank for me, and all in all this measure would be used to calculate the percentage of >=2 days open/all open.
The problem has been with using measures and I haven't been able to do like a for-loop which would go day by day that calendar and do the needed checks for all the ID's
Thanks if anyone could help me 🙂
Solved! Go to Solution.
This is called “events in progress”. https://www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/
Hi,
You may download my PBI file from here.
Hope this helps.
Thanks for your answer! This was almost the same solution I managed to do yesterday after a long struggling.
You are welcome.
This is called “events in progress”. https://www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/
Wow. That's such a clear explanation of the problem! I managed to find almost the same way for this problem myself, but this explanation really opened my eyes about what was the problem with I was struggling. Thanks!
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |