Forum Discussion

TDisco's avatar
TDisco
Icon for Helper I rankHelper 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
    Icon for Helper I rankHelper 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
      Icon for Community Support rankCommunity 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