Line Chart to display addition of total items added daily

[jibran]

Hi ,

I am working on out power bi dashboard and receive request from Managment that they want to see total number of items added daily. Currently when I do this it just filter it buy that year or month and shows items added on that particular month or year. But not total items added upto now.

As shown beow example I want to see total no of items added upto that day. 

Please can some one help me out as I am currently struglling to do this task

[v-qiuyu-msft · Accepted solution]

Hi @jibran,

To calculate cumulative data for each year, you can create a measure like below:

Cumulative Quantity =
CALCULATE (
    COUNT( Query4[ID] ),
    FILTER (
        ALL(  'Query4' ),
        YEAR('Query4'[DateAdded])<= YEAR(MAX ( 'Query4'[DateAdded]))
    )
)

Best Regards,
Qiuyun Yu

[v-qiuyu-msft]

Hi @jibran,

In your scenario, you can create a measure to calculate the cumulative total like below:

Cumulative Quantity =
CALCULATE (
    SUM ( Table1[Amount] ),
    FILTER (
        ALL ( 'Table1'[Date] ),
        'Table1'[Date]<= MAX ( 'Table1'[Date])
    )
)

Best Regards,
Qiuyun Yu

[jibran]

Hi Qiuyun,

I used same dax formula in my scenerio still it is pulling up count of total as of last year not of all years.

Sorry just to rectify from my previous message that I would like to use count not sum.

So my formula was like this 

Cumultative = CALCULATE(COUNT(Query4[ID]),Filter(ALL(Query4[DateAdded]),Query4[DateAdded] <= MAX(Query4[DateAdded])))

But still it is pulling up count of total as of that year only not of all years as shown below. It should be showing 20943.

I think I am missing something on above formula 😞

Regards

[v-qiuyu-msft · Accepted solution]

Hi @jibran,

To calculate cumulative data for each year, you can create a measure like below:

Cumulative Quantity =
CALCULATE (
    COUNT( Query4[ID] ),
    FILTER (
        ALL(  'Query4' ),
        YEAR('Query4'[DateAdded])<= YEAR(MAX ( 'Query4'[DateAdded]))
    )
)

Best Regards,
Qiuyun Yu

[jibran]

Hi ,

Sorry if I explained wrong I would like to calculate Total of all years. 

I can get cumultative of each year but I want to calcualte of all years .

[v-qiuyu-msft · Accepted solution]

Hi @jibran,

Assume the first year is 2013, in the chart, in 2013 display the count value in 2013, in 2014 display the total count value in 2013 and 2014,..,etc, right?  You can download the attached .pbix file in my previous post.

Best Regards,
Qiuyun Yu

[Vvelarde]

hi @jibran

If you want to show the cumulative by year you need filter by this.

Cumulative =
CALCULATE (
    COUNT ( Query4[ID] ),
    FILTER (
        ALL ( Query4[DateAdded].[Year] ),
        Query4[DateAdded].[Year] <= MAX ( Query4[DateAdded].[Year] )
    )
)