Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!