Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Kareus
Helper I
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.

 

Kareus_0-1708988967210.png

 

Thank you!

 

1 ACCEPTED SOLUTION

Hi,

This measure works

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

Hope this helps.

Ashish_Mathur_0-1709003627216.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

16 REPLIES 16
Ashish_Mathur
Super User
Super User

Hi,

Why should the answer be 6?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Kareus_0-1708991181525.png

 

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
https://www.linkedin.com/in/excelenthusiasts/

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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

I'll need the file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Sorry, stupid question.  How do I share it?

 

Kareus_0-1709002538827.png

 

Google Drive/One Drive.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Google Drive It Is

Hi,

This measure works

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

Hope this helps.

Ashish_Mathur_0-1709003627216.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

Kareus_0-1709006191066.png

 

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

 

Kareus_0-1709036007434.png

 

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
https://www.linkedin.com/in/excelenthusiasts/

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)

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors