Forum Discussion

drwillia's avatar
drwillia
Icon for Helper I rankHelper 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 answer would be 2 times. I may have upto 5 dates that the Current ARTS crosses. 

 

Equipment IDOriginal RTSCurrent ARTSActual ARTSDate 1Date 2
12345Wednesday, 25 February 2015Tuesday, 23 December 2014Tuesday, 20 January 2015Sunday, 1 December 2014Sunday, 14 December 2014

 

Would appreciate any assitance in this space

Thanks

 

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.

     

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity 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
      Icon for Helper I rankHelper 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

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    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.