Forum Discussion

TDisco's avatar
TDisco
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 th...
Need Help
Tips and Tricks
  • v-deddai1-msft's avatar
    v-deddai1-msft
    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())

    Capture3.PNG

    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

TDisco's avatar
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's avatar
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())

Capture3.PNG

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