Count new dates only

[spb]

First time submitting query, so here goes..

Attempting to count when an Actual Start Date first appears in the data for each Activity. 

See snapshot of the data per image below.

I am looking to be able to create visualisation showing the number count of new Starts for each reporting period.

Expected result is May 22 - 1, June 22 - 1, July 22 - 2.

[Fmaster] Table
Activity ID Reference	Actual Start Date	Reporting period end
1		31/05/2020
2	12/10/2020	31/05/2020
3		31/05/2020
4		31/05/2020
5		31/05/2020
1		30/06/2020
2	12/10/2020	30/06/2020
3	15/06/2020	30/06/2020
4		30/06/2020
5		30/06/2020
1		31/07/2020
2	12/10/2020	31/07/2020
3	15/06/2020	31/07/2020
4	3/07/2020	31/07/2020
5	22/07/2020	31/07/2020

[v-yadongf-msft · Accepted solution]

Hi @spb ,

Please try following DAX to create a new column:

count new starts = 
var _count =
CALCULATE(
    COUNT([Actual Start Date]),
    FILTER(
        'Sheet11',
       [Reporting period end] <= EARLIER('Sheet11'[Reporting period end])
        && [Actual Start Date]=EARLIER('Sheet11'[Actual Start Date])
    )
)
var _if = IF(_count = 1,1)
return _if

Create a table visual, you will get result you want:

Best regards,

Yadong Fang

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

[amitchandak]

@spb , Try a new measure like

New Meausre =
Var _start = calculate(distinctCOUNT(Table, [Activity ID]))
var _Before = calculate(distinctCOUNT(Table, [Activity ID]), filter(Allselected(Table),[Activity ID] = max(Table[Activity ID]) && Table[Reporting Date] < max(Table[Reporting Date]) ))
return
Sumx(Values([Activity ID]),_start - _Before)

[spb]

Thanks for the response and suggested measure. A error message generates without providing a result. Image provided below.

Also please note I incorrectly said May 22 report end period to generate a result of 1, but it should be 0 as it is not the first time Activity 2 had the Start Date (as would have appeared in a earlier reporting month not shown in the sample data). Activity 2 source data should show Start Date as 12/10/19, not 12/10/20. Apologies. Happy to post a small sample PBI file, but not sure can do in this space.

Thanks again.

Error message generating when using measure