Forum Discussion

drwillia's avatar
drwillia
Helper I
3 years ago
Solved

Count How Many Times a Date Crosses A Milestone

Hi   I would like to count the number of times that a date crosses a milestone.   For example in the below table the  CURRENT ARTS from the ORIGINAL RTS crosses Date 1 and Date 2 so therefore the...
Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, drwillia 

     

    You can try the following methods.

    In the Power Query, Transform-Unpivot other column:

    Result:

    Measure = 
VAR _org_rts = CALCULATE(MAX('Table'[Value]), FILTER('Table','Table'[Attribute]="Original RTS"))
VAR _cur_arts = CALCULATE(MAX('Table'[Value]), FILTER('Table','Table'[Attribute]="Current ARTS"))
VAR _table = FILTER('Table',LEFT('Table'[Attribute],4)="Date")
Return
COUNTROWS(FILTER(_table,'Table'[Value]>=_org_rts&&'Table'[Value]<=_cur_arts))

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

drwillia Not quite understanding this. Do you mean that since the Current ARTS date is greater than both Date 1 and Date 2 that it "crosses" those those dates? 

drwillia's avatar
drwillia
Helper I
3 years ago

Hi Greg,

 

yes but the original RTS date was also before those two dates. So the current RTS date has progressed from the orignal date to the current date and crossed over Date 1 and Date 2 in doing so.

 

Hoep that makes sense

Thanks