Cumulative Distinct Counts Dual Axis Line Viz

[TDisco]

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

[amitchandak]

@TDisco , Check if this can blog help -https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[TDisco]

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 · Accepted 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