cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper I

## Running Total Help

How can I determine the first intance the running total is less than or equal to 0?

In this case I want the result to be 6.

As its the first week that is non-zero.

Thank you!

1 ACCEPTED SOLUTION
Super User

Hi,

This measure works

``Measure = COUNTROWS(FILTER(VALUES('Resource Priority'[RT Good Week]),[10K 2PLD RT]>0))+1``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
16 REPLIES 16
Super User

Hi,

Why should the answer be 6?

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Super User

That is the first instance when the RT is <0.  In your original post, you mentioned that you wanted to know the first instance when the RT is >0.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

You are correct, I mispoke.  I'll fix it in the original request.

Super User

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I
Here is my RT measure ...

10
K 2PLD RT = SWITCH(TRUE(),
CALCULATE(SUM('Resource Priority'[10K 2PLD])-('Manual Inputs'[VALUE Weekly 10k]*([MEASURE RT Good Week Count])),
FILTER(ALLSELECTED('Resource Priority'[RT Good Week]), ISONORAFTER('Resource Priority'[RT Good Week], MAX('Resource Priority'[RT Good Week]), DESC))) >= -[VALUE Weekly 10k],

[Week Position %]*'Manual Inputs'[VALUE Weekly 10k]+
CALCULATE(SUM('Resource Priority'[10K 2PLD])-('Manual Inputs'[VALUE Weekly 10k]*([MEASURE RT Good Week Count])),
FILTER(ALLSELECTED('Resource Priority'[RT Good Week]), ISONORAFTER('Resource Priority'[RT Good Week], MAX('Resource Priority'[RT Good Week]), DESC))),

CALCULATE(SUM('Resource Priority'[10K 2PLD])-('Manual Inputs'[VALUE Weekly 10k]*([MEASURE RT Good Week Count]))
))
Super User

I'll need the file.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Sorry, stupid question.  How do I share it?

Super User

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Super User

Hi,

This measure works

``Measure = COUNTROWS(FILTER(VALUES('Resource Priority'[RT Good Week]),[10K 2PLD RT]>0))+1``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Works, for counting ALL dates less than or equal to zero.

BUT, I'm looking for the number of weeks to the FIRST and only the FIRST negative value.

Super User

I do nto understand your expected result.  Show that very clearly.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

I'm trying to figure out the periods of time (weeks) to the green box of each running total.

For the Wide RT column.  Your formula is returning 3.  It should be 1 as the frist row is negative.

I listed the correct answer in green below the "Lead Time - Weeks" table.

Super User

Hi,

I can try but before that you will need to create a Calendar Table in the file with a week column in there.  Create a relationship (Many to One and Single) from the Good Build Date column to the Date column of the Calendar Table.  Share the download link of the revised PBI file.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Does this reply above make sense Ashish?

This YouTube video is a really close solution, but my Running Total is a measure.

So CALCULATEDTABLE(VALUES (Sales[Order Date]), ALLSELECTED()) does work from the video.

Counting consecutive days with sales - Unplugged #47 (youtube.com)