Show intermediate sum result

[MatevzP]

Hello everyone,

First, let me excuse myself for probably a very basic question. Also, English is not my native language so i'm not even sure the title is correct, but let me show what i need, it'll be the easiest.

So i have an excel document showing number of documents uploaded on each day. I'd like to show a graph that is constantly increasing, not actual values per day.

Since my table doesn't include a value of actual documents uploaded on each day, i go through and instead of CALCULATE(SUM()), i use COUNT, and count document titles. Same end result: total sum is correct, but as i said, i'd like to get a graph that is only ever going up, not up and down, for the lack of better words.

In Java, i'd do a loop, use a temp integer variable and then increase integer on each cycle and print it out.
But i'm not sure how to approach the problem with DAX.

(Just a visual representation of what i think is going on, not actual table, i only have one table with data)

Thank you in advance and have a nice day 🙂

[amitchandak · Accepted solution]

@MatevzP , using date table joined to you table (date from date table used in visual

Cumm = CALCULATE(SUM(Table[documents]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

or

Cumm = CALCULATE(SUM(Table[documents]),filter(allselected('Table'),'Table'[date] <=max('Table'[date])))

[tamerj1]

Hi @MatevzP 

this is called Runnimg Total or Comulative Count. You can try

# Documents RT =
CALCULATE (
    DISTINCTCOUNT ( Table[Folder Name and Path] ),
    Table[Last Upload] <= MAX ( Table[Last Upload] )
)

[MatevzP]

Hi,

Thanks for your reply as well, but answer from Mr. @amitchandak works better in my scenario.

I did two versions of your DAX, one distinct and one just count.

DISTINCTCOUNT (folder paths are always the same, hence 1)

COUNT (counts, but as originally, goes up and down)

DAX:

test 2 = 
CALCULATE(
    COUNT(WP1_Report[Folder Name and Path]),
    WP1_Report[Last Upload] <= MAX(WP1_Report[Last Upload])
)

test 2 = 
CALCULATE(
    DICTINCTCOUNT(WP1_Report[Folder Name and Path]),
    WP1_Report[Last Upload] <= MAX(WP1_Report[Last Upload])
)

[tamerj1 · Accepted solution]

@MatevzP 

In your chart, you're still using the date from the same table, right?

try this code

test 3 =
CALCULATE (
    COUNTROWS ( WP1_Report ),
    ALLEXCEPT ( WP1_Report, WP1_Report[Review Status] ),
    WP1_Report[Last Upload] <= MAX ( WP1_Report[Last Upload] )
)

[MatevzP]

This one behaves the same as original DAX from amitchdak.
Both work! 🙂
Yours is a bit simpler. Thank you! 🙂

[amitchandak · Accepted solution]

@MatevzP , using date table joined to you table (date from date table used in visual

Cumm = CALCULATE(SUM(Table[documents]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

or

Cumm = CALCULATE(SUM(Table[documents]),filter(allselected('Table'),'Table'[date] <=max('Table'[date])))

[MatevzP]

Ok, it works!

I took your DAX formula and somehow applied to my scenario. Not exactly sure how it works, but it does.

month, ok

DAX:

test = CALCULATE(COUNT(WP1_Report[Last Upload]), FILTER(ALLSELECTED(WP1_Report[Last Upload].[Date]), WP1_Report[Last Upload].[Date]<=MAX(WP1_Report[Last Upload])))

[MatevzP]

Hi, thank you for your reply!

It seems like (to my untrained eye), that you are working with two tables here. One being table[documents] and another one being table[date].

In reality, i only have one table. I probably didn't explain it well in OP.
This is how it looks like: