Forum Discussion

spb's avatar
spb
Frequent Visitor
4 years ago
Solved

Count new dates only

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 DateReporting period end
1 31/05/2020
212/10/202031/05/2020
3 31/05/2020
4 31/05/2020
5 31/05/2020
1 30/06/2020
212/10/202030/06/2020
315/06/202030/06/2020
4 30/06/2020
5 30/06/2020
1 31/07/2020
212/10/202031/07/2020
315/06/202031/07/2020
43/07/202031/07/2020
522/07/202031/07/2020
Need Help
  • v-yadongf-msft's avatar
    v-yadongf-msft
    4 years ago

    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.

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      spb
      Frequent Visitor
      4 years ago

      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

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Community Support
    4 years ago

    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.