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.
I'm tracking projects. They have an ID, Status (Opened, Closed), Start Date (2019 and 2020), End Date (2020 only). To display some other data, I had to pivot the table on 4 columns (not related to this question). I've been looking for hours and can't find anything that does all the following:
- Single table that has been pivoted.
- Have 'Date' Table (having issue with two date columns that need counted, but I'm only 'allowed' one Date table)
- Not wanting to rolling count dollars, just count distinctive rows
(example actual hand made viz from manually built table)
Data Set
Project | Status | Start | End |
1 | Open | 1/1/2019 | |
1 | Open | 1/1/2019 | |
1 | Open | 1/1/2019 | |
1 | Open | 1/1/2019 | |
2 | Open | 12/2/2019 | |
2 | Open | 12/2/2019 | |
2 | Open | 12/2/2019 | |
2 | Open | 12/2/2019 | |
3 | Open | 1/1/2020 | |
3 | Open | 1/1/2020 | |
3 | Open | 1/1/2020 | |
3 | Open | 1/1/2020 | |
4 | Open | 2/1/2020 | |
4 | Open | 2/1/2020 | |
4 | Open | 2/1/2020 | |
4 | Open | 2/1/2020 | |
5 | Open | 2/1/2020 | |
5 | Open | 2/1/2020 | |
5 | Open | 2/1/2020 | |
5 | Open | 2/1/2020 | |
6 | Open | 3/1/2020 | |
6 | Open | 3/1/2020 | |
6 | Open | 3/1/2020 | |
6 | Open | 3/1/2020 | |
7 | Closed | 1/1/2019 | 2/1/12020 |
7 | Closed | 1/1/2019 | 2/1/12020 |
7 | Closed | 1/1/2019 | 2/1/12020 |
7 | Closed | 1/1/2019 | 2/1/12020 |
8 | Closed | 6/1/2019 | 3/1/2020 |
8 | Closed | 6/1/2019 | 3/1/2020 |
8 | Closed | 6/1/2019 | 3/1/2020 |
8 | Closed | 6/1/2019 | 3/1/2020 |
9 | Closed | 1/1/2020 | 4/1/2020 |
9 | Closed | 1/1/2020 | 4/1/2020 |
9 | Closed | 1/1/2020 | 4/1/2020 |
9 | Closed | 1/1/2020 | 4/1/2020 |
10 | Closed | 2/2/2020 | 7/1/2020 |
10 | Closed | 2/2/2020 | 7/1/2020 |
10 | Closed | 2/2/2020 | 7/1/2020 |
10 | Closed | 2/2/2020 | 7/1/2020 |
Expected Outcomes
Project Distinct Cumulative Count - Opened | Month | |
1 | Jan | |
3 | Feb | |
4 | Mar | |
Project Distinct Cumulative Count - Closed | Month | |
1 | Feb | |
2 | Mar | |
3 | Apr | |
3 | May | No Change |
3 | June | No Change |
4 | July |
Solved! Go to Solution.
Hello @TDisco ,
Create a new table that contains all the months of 2020:
Table 2 = CALENDAR(DATE(2020,1,1),DATE(2020,12,31))
Next, create two measures to open and close:
Opened = IF(MONTH(MAX('Table 2'[Date]))<= MONTH(TODAY()),CALCULATE(DISTINCTCOUNT('Table'[Project]),FILTER(ALL('Table'),'Table'[Status] = "Open" &&MONTH('Table'[Start])<=MONTH(MAX('Table 2'[Date])) && YEAR('Table'[Start]) = 2020)),BLANK())
Closed = IF(MONTH(MAX('Table 2'[Date]))<= MONTH(TODAY()),CALCULATE(DISTINCTCOUNT('Table'[Project]),FILTER(ALL('Table'),'Table'[Status] = "Closed" &&MONTH('Table'[End])<=MONTH(MAX('Table 2'[Date])) && YEAR('Table'[End]) = 2020)),BLANK())
For more information, see: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EbHcCEyldfdGhGA5ta...
If this post helps, then consider Accepting it as the solution to help other members find it more quickly.
Best regards
Dedmon Dai
@TDisco , Check if this can blog help -https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
hmm... have some data showing (thank you) how do I convert countx into DistinctCount?
Hello @TDisco ,
Create a new table that contains all the months of 2020:
Table 2 = CALENDAR(DATE(2020,1,1),DATE(2020,12,31))
Next, create two measures to open and close:
Opened = IF(MONTH(MAX('Table 2'[Date]))<= MONTH(TODAY()),CALCULATE(DISTINCTCOUNT('Table'[Project]),FILTER(ALL('Table'),'Table'[Status] = "Open" &&MONTH('Table'[Start])<=MONTH(MAX('Table 2'[Date])) && YEAR('Table'[Start]) = 2020)),BLANK())
Closed = IF(MONTH(MAX('Table 2'[Date]))<= MONTH(TODAY()),CALCULATE(DISTINCTCOUNT('Table'[Project]),FILTER(ALL('Table'),'Table'[Status] = "Closed" &&MONTH('Table'[End])<=MONTH(MAX('Table 2'[Date])) && YEAR('Table'[End]) = 2020)),BLANK())
For more information, see: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EbHcCEyldfdGhGA5ta...
If this post helps, then consider Accepting it as the solution to help other members find it more quickly.
Best regards
Dedmon Dai
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 |
---|---|
116 | |
102 | |
78 | |
76 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |