Forum Discussion
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...
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.
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.
Hi v-zhangti Greg_Deckler thanks very much guys, much appreciated!