Line Graph count of cumulative value above 0

[Kmcharti]

Hello Power BI Novice seeking a little help.

I am looking to create a line graph with a duration from 4/1/23 - 9/30/23. In this line graph I would like to have a count of ID's that at the day on the graph the culmulative total of inventory is greater than 0.

I provided a basic table below. I have been working far to long on something that seems simple. Please help!

For Example, on 5/1/23 the count would be 4 but 5/2 would be 3, because ID 1 sum is now 0.

ID	Date	Inventory
1	4/1/23	2
1	5/2/23	-2
2	4/1/23	1
2	4/27/23	1
2	7/1/23	-2
3	5/1/23	1
3	5/6/23	1
4	4/13/23	2
4	6/15/23	-1

[Anonymous]

Hi @Kmcharti ,

Thank you for ryan_mayu answer , and I have other suggestions for you:

Below is my test data:

You can create a column in your main table to calculate the cumulative inventory for each ID up to each date:

Cumulative Inventory = 
CALCULATE(
    SUM('Table'[Inventory]),
    FILTER(
        ALL('Table'),
        'Table'[ID] = EARLIER('Table'[ID]) &&
        'Table'[Date] <= EARLIER('Table'[Date])
    )
)

Then you can create a measure to count the IDs where the cumulative inventory is greater than 0:

Count of IDs with Positive Inventory = 
IF(SUM('Table'[Cumulative Inventory]) > 0 , 
CALCULATE(
    DISTINCTCOUNT('Table'[ID]),
    FILTER(
        ALL('Table'),
        'Table'[Cumulative Inventory] > 0
    )
),
    IF(SELECTEDVALUE('Table'[Cumulative Inventory]) = 0 , 
    CALCULATE(
    DISTINCTCOUNT('Table'[ID]),
    FILTER(
        ALL('Table'),
        'Table'[Cumulative Inventory] > 0
    )) - 1
    ,0)
)

The final output will be like this:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kmcharti]

Thank you for your responses. I was very excited to see the sample working but however when I applied it to my larger set it just gives a flat line. Which is baffling me. A link to the csv and my formulas and output below. 

Any additional help would be great. Thank you.

Count Test Link 

Cumulative Inventory =

CALCULATE(

    SUM('Count Test'[Inventory]),

    FILTER(

        ALL('Count Test'),

        'Count Test'[ID] = EARLIER('Count Test'[ID]) &&

        'Count Test'[Date] <= EARLIER('Count Test'[Date])

    )

)

Count of IDs with Positive Inventory =

IF(SUM('Count Test'[Cumulative Inventory]) > 0 ,

CALCULATE(

    DISTINCTCOUNT('Count Test'[ID]),

    FILTER(

        ALL('Count Test'),

        'Count Test'[Cumulative Inventory] > 0

    )

),

    IF(SELECTEDVALUE('Count Test'[Cumulative Inventory]) = 0 ,

    CALCULATE(

    DISTINCTCOUNT('Count Test'[ID]),

    FILTER(

        ALL('Count Test'),

        'Count Test'[Cumulative Inventory] > 0

    )) - 1

    ,0)

)

Again Thank you for your help.

-Kory

[Anonymous]

Hi @Kmcharti ,

Try changing the X-axis continuum to Categorical

Can I ask you to send the following file again, the previous one won't open, thank you.

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kmcharti]

Xianda Tang-

Sorry about that. Count Data  Here is the link again. I did try both Continous and Categorical.

Ryan-

I have not but I will today.

Thank you both!

[Anonymous · Accepted solution]

Hi @Kmcharti ,

I've just tested your data, and I see that I need to change the original measure to make it look like this:

Count of IDs with Positive Inventory = 
IF(SUM('Count Test'[Cumulative Inventory]) > 0 ,
CALCULATE(
    DISTINCTCOUNT('Count Test'[ID]),
    FILTER(
        'Count Test',
        'Count Test'[Cumulative Inventory] > 0
    )
),
    IF(SELECTEDVALUE('Count Test'[Cumulative Inventory]) = 0 ,
    CALCULATE(
    DISTINCTCOUNT('Count Test'[ID]),
    FILTER(
        'Count Test',
        'Count Test'[Cumulative Inventory] > 0
    )) - 1
    ,0)
)

The final output will be like this:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kmcharti]

Anyone able to get it to work with the large data set?

[ryan_mayu]

have you tried the solution I provided?

[ryan_mayu]

why 5/1/23 is 4?

I think we have 2 for ID 1, 1 for ID 2, 1 for ID3 and 2 for ID4. 

[Kmcharti]

5/1/23 would be 4 because that is the count of IDs that are above 0.

[ryan_mayu]

@Kmcharti 

you can try this

Column =

VAR _tbl=FILTER('Table','Table'[Date]<='Table (2)'[date])

VAR _tbl2=ADDCOLUMNS(_tbl,"check",if([Date]=maxx(FILTER(_tbl,[ID]=EARLIER('Table'[ID])),[Date]),1))

return countx(FILTER(_tbl2,[check]=1 && 'Table'[Inventory]>0),[ID])

pls see the attachment below