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 Team,
I need your help in writing a measure for calculating daily count of items.
Here's the sample data
Item | Opendate | Closeddate |
A | 7/20/2020 | 7/22/2020 |
B | 7/20/2020 | 7/20/2020 |
C | 7/21/2020 | 7/27/2020 |
I need to create virtual table like this and get result from this below table
Item | Opendate | Closeddate |
A | 7/20/2020 | 7/22/2020 |
A | 7/21/2020 | 7/22/2020 |
A | 7/22/2020 | 7/22/2020 |
B | 7/20/2020 | 7/20/2020 |
C | 7/21/2020 | 7/27/2020 |
C | 7/22/2020 | 7/27/2020 |
C | 7/23/2020 | 7/27/2020 |
C | 7/24/2020 | 7/27/2020 |
C | 7/25/2020 | 7/27/2020 |
C | 7/26/2020 | 7/27/2020 |
C | 7/27/2020 | 7/27/2020 |
In the above table i need to add dates for Item until its closed.
So, the measure has to calculate distinct count of items in each opendate.
Resultset or measure value(Distinct count on each opendate)
Opendate | distinctcount of items |
7/20/2020 | 2 |
7/21/2020 | 2 |
7/22/2020 | 2 |
7/23/2020 | 1 |
7/24/2020 | 1 |
7/25/2020 | 1 |
7/26/2020 | 1 |
7/27/2020 | 1 |
Please suggest me DAX measure to achieve this,
Thank you.
@sree_12 ,
Check if this file can help
https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0
Thank you @amitchandak for your response.
But the count seems to be incorrect in my case.
Item | Opendate | Closeddate | Category | code |
A | 7/20/2020 | 7/22/2020 | sales | x |
A | 7/21/2020 | 7/22/2020 | sales | x |
A | 7/22/2020 | 7/22/2020 | sales | x |
B | 7/20/2020 | 7/20/2020 | Purchase | y |
C | 7/21/2020 | 7/27/2020 | purchase | y |
C | 7/22/2020 | 7/27/2020 | purchase | x |
C | 7/23/2020 | 7/27/2020 | purchase | x |
C | 7/24/2020 | 7/27/2020 | purchase | x |
C | 7/25/2020 | 7/27/2020 | purchase | x |
C | 7/26/2020 | 7/27/2020 | purchase | y |
C | 7/27/2020 | 7/27/2020 | purchase | y |
But I forgot to mention if "closed date" is null, add date to opendate until the item is closed and also get the distintinct count by including category column while summarizing.
Please let me know your suggestions.!
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 |
---|---|
39 | |
25 | |
20 | |
14 | |
8 |
User | Count |
---|---|
71 | |
47 | |
46 | |
20 | |
16 |