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...
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.