Distinct Count

[Craigc3814]

I have 3 tables

One is all open work that I currently have (a work item number is on this table and it gives me the date the item was opened)
Two is all work that has been Closed within the past year (work item number is on this table but it also gives me the date that item # was initiated aka Opened)
Third is just a date table

I want to distinct count all work item numbers on the Open table by Open date
I want to distinct count all work item numbers on the Closed table by initiated date
I then want to add them together and land them in a pivot table that tells me how many work item numbers were open per month (obviously using the date table).

For some reason I cannot wrap my head around the best way to do this

 

[Puertorekinsam]

you can use something like this in a column to the side of the table:

=IF(COUNTIF($B$1:B2,B2)>1,0,1)

this will give you a one the first time a work number appears.

Then you can use a sumif.. lets' say the formula above was in column C with the work numbers in B and the original dates in A.

Then lets assume the Dates for your third table were in column J, you can put this in column K and pull down:

=SUMIFS($C:$C,$A:$A,J1)

[/code]

 

[DRSteele]

One thing I know is that Distinct Count becomes available in the Value Field Settings of a Pivot Table if the 'Add this data to the Data Model ' checkbox is selected when creating the PT.

 

[Craigc3814]

This is how you do it in Excel, I need to do it in PQ or Dax