HELP !! simple calculation ?!

[RvdHeijden]

Goodday,

I need a calculation which sums up the total of a certain column.

For example the column 'Aantal HP gerealiseerd' has a number of HP's per week but i need a column that shows the cumulative per week.

i think it's pretty simple but im not sure how to do it

For example

2018-35             186

2018-36             418  (186 + 232)

2018-37             727  (418 + 309)

2018-38             etc

[BobBI]

Hi ,

There several method to calculate cumulative total , however considering your sample data , this DAX will is simple way to achieve the desired output.

Your Data (table = Rve Data)

Col1   Col2  Number

2018   35       186

2018   36       232

2018   37       309

2018   38       150

2018   39       215

First of all i created a measure called 'Total Number' = sum('Rvd Data'[number])

Second Measure called Cumulative number ( Dax below)

Cumulative number = CALCULATE ([Total number],
                                FILTER(
                                        ALL('Rvd Date'),
                                        'Rvd Date'[Col2]<=MAX('Rvd Date'[Col2])
                                      )
                                 )

Hope this helps,

Cheers

SS

[RvdHeijden]

@BobBI thanks for the reply but it doesn't work just yet.

Ive added the measure and the formula but it just returns the same value on all rows.

The only thing i can see is that you have 2 colums for the Year-Week (Col1 and Col2) and i have one column (Week)

Totaal aantal HP = SUM(Forecast[Aantal HP gerealiseerd])

Cumulatieve HP gerealiseerd =
CALCULATE ([Totaal aantal HP];
FILTER(
ALL(Forecast);
'Forecast'[Week]<=MAX(Forecast[Week])
)
)

[Joorge_C]

Hello Make sure your Week Column you are using, comes from a Date Defined Table, in other words there must be a Column with a Date Value and this week must be a derived Column of that Date (then use Date instead of Week in the Formula)

To make sure you are using a Date Defined table, right click the dataset and select Define as Date Table) in the fields section.

[BobBI]

hi Rvd,

can you split 2018-35  by  '-' ?  because '2018-35' is become a text as it contains hiphen (-) sign. if you split them or use any month or date fields with numeric or date data type , then this would work.

forcast week number should be numeric . and check the values in table visual as it would summarise the amount value by week.

calcuated column wouldn't help.

Thanks,

SS

[Barnee]

Hi @RvdHeijden,

try to create a measure out of the Cumulative HP gerealiseerd calculation instead of a calculated column.

Regards,

Barna 

[RvdHeijden]

@Barnee 

How ? 

Besides i need this data for a bar or a line graph so does that still work if i use a measure ?

[Barnee · Accepted solution]

@RvdHeijden,

Modeling -> New measure, and copy the code: 
Cumulatieve HP gerealiseerd =
CALCULATE ([Totaal aantal HP];
FILTER(
ALL(Forecast);
'Forecast'[Week]<=MAX(Forecast[Week])
)

It will work as a line or bar graph. Put a Date dimension on the Axis and your newly created measure to Values.
For me it looks like this:

[RvdHeijden]

@Barnee it made a few changes and now it works.

The trick was that i was still using a calculated column in the visual instead of the measure

[RvdHeijden]

@Barnee

It still doesn't work, it still returns the same value and ive copied your formula.

The measure for Totaal aantal HP is different i think because you use 2 columsn Col1 and Col2 and i have 1 colum

[mikeborg82]

It sounds like you need a semi-additive measure.  

You would need a date dimension table, but then a formula like:

Total : = 

CALCULATE (

       [FIELD],

       LASTDATE ( 'Date' [Date] )

)

[RvdHeijden]

@mikeborg82

I have a 'date' table so that is ok but im not sure what to fill in at the [Field] part of the calculation.

Can you tell me ? i tried a few things but it's doesn't work just yet

Cumulatieve HP gerealiseerd=
CALCULATE (
[FIELD];
LASTDATE ( 'Date'[Date] )
)