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 a cumulative distinct count of the number of projects that have a Start Date in 2020, by month.IE: Jan = 1 (count 1), Feb = 4 (count 5), Mar =1 (count 6), Apr = 0 ( add and count 6)If no projects created for that month, add the month to the data set and use the previous valuea cumulative distinct count of the number of projects that have a End Date in 2020, by month.Starts any year, but ends in 2020If no projects ended that month, add the month to the data set and use the previous valuea Line Chart that displays two lines, Open and Closed - by month - 2020 only.Stops at current month (doesn't go all the way through December, since there are no values)(example actual hand made viz from manually built table) Final Chart Data SetProjectStatusStartEnd1Open1/1/2019 1Open1/1/2019 1Open1/1/2019 1Open1/1/2019 2Open12/2/2019 2Open12/2/2019 2Open12/2/2019 2Open12/2/2019 3Open1/1/2020 3Open1/1/2020 3Open1/1/2020 3Open1/1/2020 4Open2/1/2020 4Open2/1/2020 4Open2/1/2020 4Open2/1/2020 5Open2/1/2020 5Open2/1/2020 5Open2/1/2020 5Open2/1/2020 6Open3/1/2020 6Open3/1/2020 6Open3/1/2020 6Open3/1/2020 7Closed1/1/20192/1/120207Closed1/1/20192/1/120207Closed1/1/20192/1/120207Closed1/1/20192/1/120208Closed6/1/20193/1/20208Closed6/1/20193/1/20208Closed6/1/20193/1/20208Closed6/1/20193/1/20209Closed1/1/20204/1/20209Closed1/1/20204/1/20209Closed1/1/20204/1/20209Closed1/1/20204/1/202010Closed2/2/20207/1/202010Closed2/2/20207/1/202010Closed2/2/20207/1/202010Closed2/2/20207/1/2020 Expected Outcomes Project Distinct Cumulative Count - OpenedMonth 1Jan 3Feb 4Mar Project Distinct Cumulative Count - ClosedMonth 1Feb 2Mar 3Apr 3MayNo Change3JuneNo Change4July

Helper I

6 years ago

Solved

Cumulative Distinct Counts Dual Axis Line Viz

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

a cumulative distinct count of the number of projects that have a Start Date in 2020, by month.
- IE: Jan = 1 (count 1), Feb = 4 (count 5), Mar =1 (count 6), Apr = 0 ( add and count 6)
- If no projects created for that month, add the month to the data set and use the previous value
a cumulative distinct count of the number of projects that have a End Date in 2020, by month.
- Starts any year, but ends in 2020
- If no projects ended that month, add the month to the data set and use the previous value
a Line Chart that displays two lines, Open and Closed - by month - 2020 only.
- Stops at current month (doesn't go all the way through December, since there are no values)

(example actual hand made viz from manually built table)

Final Chart

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

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/EbHcCEyldfdGhGA5taClZ7IBhR_DkJVxRxziKTrmWnJrlQ?e=WYX5JU

If this post helps, then consider Accepting it as the solution to help other members find it more quickly.

Best regards

Dedmon Dai

3 Replies

amitchandak
Super User
6 years ago
TDisco , Check if this can blog help -https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
- TDisco
  Helper I
  6 years ago
  hmm... have some data showing (thank you) how do I convert countx into DistinctCount?
  
  _Current Employees = CALCULATE(COUNTx(FILTER('tbl_Projects_Pivot','tbl_Projects_Pivot'[StartDate]<=max('Date_Start'[Date]) && (ISBLANK('tbl_Projects_Pivot'[ActualEndDate]) || 'tbl_Projects_Pivot'[ActualEndDate]>max('Date_Start'[Date]))),('tbl_Projects_Pivot'[Id])))
  
  and
  
  _Last Period Employee =
  var _min_date = minx(all('Date_Start'),'Date_Start'[Date])
  var _Expression=if(ISFILTERED('Date_Start'[Month]),maxx('Date_Start',DATEADD('Date_Start'[Date],-1,MONTH)),maxx('Date_Start',DATEADD('Date_Start'[Date],-1,YEAR)))
  Return
  CALCULATE(COUNTx(FILTER('tbl_Projects_Pivot','tbl_Projects_Pivot'[StartDate]<=_Expression && 'tbl_Projects_Pivot'[StartDate]>=_min_date && (ISBLANK('tbl_Projects_Pivot'[ActualEndDate]) || 'tbl_Projects_Pivot'[ActualEndDate]>_Expression)),('tbl_Projects_Pivot'[Id])),CROSSFILTER('tbl_Projects_Pivot'[ActualEndDate],'Date_Start'[Date],None))
  - v-deddai1-msft
    Community Support
    6 years ago
    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/EbHcCEyldfdGhGA5taClZ7IBhR_DkJVxRxziKTrmWnJrlQ?e=WYX5JU
    
    If this post helps, then consider Accepting it as the solution to help other members find it more quickly.
    
    Best regards
    Dedmon Dai

Forum Discussion

Cumulative Distinct Counts Dual Axis Line Viz

3 Replies

Related content

count distinct

Cumulative Count DAX

Cumulative distinct count

Cumulative distinct count

Distinct Count