cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Cycle Count

 

Hi All.


I have my data in Column A and B with state 1 & 0 below.

I would like to have result of Column C & D (highlighted in red) based on Column A and B state.

The result in Column C & D gives me idea on how many cycle count it have in Column A and B, as shown in Column F & G. 

Other rule, the cycle count is only incremented when both Column A & B have state "0" value and the next row of either Column A or B indicates a state "1" value .

Please I will appreciate your help for any idea since I would like to apply this formula in a larger data.

count.JPG
Thank you in advance,

 
1 REPLY 1
Highlighted
Super User II
Super User II

Re: Cycle Count

Thanks for posting an interesting problem
I created a small table like yours. I added a calculated column that added up the left and right columns. You'll see why when we get to the measure.
leftplusright.PNG

Since we need to access "the next row" and DAX has no built in concept of "next" or "previous" I also

added an index column in the query editor 

 

addIndex.PNG

 

 

 

 

 

 

 

 

Now we need to write the measure that calcuates whether a given row should be counted as a cycle

Is Cycle =
VAR cur_index =
    SELECTEDVALUE ( Cycle[Index] )
VAR cur_both =
    SELECTEDVALUE ( Cycle[leftPlusRight] )
VAR next_row_is_one =
    CALCULATE (
        COUNTROWS ( cycle ),
        ALL ( Cycle ),
        Cycle[Index] = cur_index + 1,
        Cycle[leftPlusRight] > 0
    )
VAR count_as_cycle = cur_both = 0
    && next_row_is_one = 1
RETURN
    count_as_cycle

.We store the index value from the current filter context and the "left plus right" value as local variables.  Then we use CALCULATE to count the rows where the next row  has an index one greater (the next row) and a leftplusright is greater than zero (either 1 or 2)
then we return the combination of the next row being at least one and the current row being zero to say whether or not it should be counted as a cycle.

We can put this in a table graphic to check our results 
iscycle.PNG

This code does not calculate whether the 1 in the "next" row is in the left or right column, it could be changed to do so.

 

 





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors