Running Total Help

[Kareus]

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!

[Ashish_Mathur]

Hi,

Why should the answer be 6?

[Kareus]

[Ashish_Mathur]

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.

[Kareus]

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

[Ashish_Mathur]

Share the download link of the PBI file with the RT measure already written.

[Kareus]

Here is my RT measure ...

10K 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]))

))

[Ashish_Mathur]

I'll need the file.

[Kareus]

Sorry, stupid question.  How do I share it?

[Ashish_Mathur]

Google Drive/One Drive.

[Kareus]

Google Drive It Is

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

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

Hope this helps.

[Kareus]

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.

https://drive.google.com/file/d/1BnqZDDm8UjoIObpoM-m5tWv31EwLx-K6/view?usp=sharing

[Ashish_Mathur]

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

[Kareus]

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.

[Ashish_Mathur]

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.

[Kareus]

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)