Cumulative total by week number

[Anonymous]

I want to produce a line chart that shows the cumulative count of statuses by week number. I can get it to show the count per week, but I am struggling to change this to show the cumulative amount. This is my line chart at the moment:

This is my table:

 

Can anyone help me to get the line chart to show the running total instead of the count per week? (I am new to Power BI!!)

Thanks!

[GilbertQ]

Hi there you should be able to use the following DAX syntax below.

Cumulative Part Sales =

Cumulative Part Sales = 
CALCULATE (
    SUM ( 'Parts Sales Line'[Line Amount] ),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Parts Sales Line'[Sales Order Created Date] )
    )
)

 As long as you have got your relationship between your date table and your fact table, you MUST then drag in the column from the Date Table (Week) and it should display correctly.

[v-yulgu-msft]

Hi, @Anonymous

In your scenario, use weeknumber as Axis, use statusDescription as Legend and use the below measure as Value

Cumulative Statuses = 
CALCULATE (
SUM( 'Cumulative total'[Coumt]),
FILTER ( ALL( 'Cumulative total' ),'Cumulative total'[weeknumber] <= MAX ( 'Cumulative total'[weeknumber] ) )
)

Best regards,
Yuliana Gu

[Anonymous]

Hi @v-yulgu-msft

I don't have a cumulative total table - do I need to create this?

Thanks,

Heather

[v-yulgu-msft]

Hi, @Anonymous

In my test, I named the table you provided in your original post as cumulative total table. So, you don't need to create this, just replace the table name with yours in the formula.

Thanks,
Yuliana Gu

[Anonymous]

Thank you @v-yulgu-msft!

This is the closest yet - however it is showing each status with the same count for each week?

H

[BhaveshPatel]

Cumulative Statuses :=
CALCULATE (
 COUNT( Table1[Status Description] );
 FILTER ( ALL ( Table1 ); Table1[Time Stamp] <= MAX ( Table1[Time Stamp] ) );
 VALUES ( Table1[Week number] )
)

Try this measure and It would solve your problem. 

[spuder]

Hi Bhavesh,

I tried to create some sample data and tried your measure. Unfortunetaly I didn't get the right result. But when I erased the second filter of the CALCULATE() function it worked. 

I read that the VALUES() function bring back the filter. So in my understanding he counts only the values of the weeknum. And that was what happened. Am I right with my thoughts or is there something I can't see. 

Thanks in advance. 

[Anonymous]

Thank you. I didn't have a calendar table in my model. I have created one and linked it to my status history table on the timestamp column.

I have created the measure however when I try to use it in a visualisation it gives me the error message: Calculation error in measure 'OrgStatusHistory'[Cumulative statuses]: The function COUNT takes an argument that avaluates to numbers or dates and cannot work with values of type string.

Any ideas how to fix that?

Thanks for your help 🙂

[BhaveshPatel]

Sorry my mistake. Use COUNTA instead as your data type for the status column is string.

[Anonymous]

It now let's me use the measure but it is an empty column so it isn't calculating?

My measure is:

Cumulative statuses = IF (
MIN ( 'Calendar'[Date] )
<= CALCULATE ( MAX ( OrgStatusHistory[Timestamp] ), ALL ( OrgStatusHistory ) ),
CALCULATE (
COUNTA ( OrgStatusHistory[Status Description] ),
FILTER (
ALL ( 'Calendar'[Date] ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)
)
)

[spuder]

CALCULATE (COUNTA(OrgStatusHistory[Status Description]),
FILTER (ALL (OrgStatusHistory),OrgStatusHistory[Timestamp]<= MAX('Calendar'[Date]))

Maybe this will help you. And keep in mind that you have to relate your DateTable

Greetings