Show YTD as total column

[rh205bip]

Hi,

I hope somebody can help me with this problem.         

Example data:

Name | Month | # Hours

 X              JAN          8

 X               FEB         8

I would like to have an extra column in this pivot table with # Hours (YTD). When i use this measure: Num((Sum({<Year={$(=Max(Year))}, Month={'<=$(=Max(Month))'}>}[# Hours]) the pivot table should look like this, when filtering on month FEB:

Month : FEB|2018

Name |  # Hours | # Hours YTD

        X              8                   16

...but what i get is this:

Month : JAN|2018                                             FEB|2018

Name |  # Hours | # Hours YTD                    Name |  # Hours | # Hours YTD

        X              0                   0                                     X              8                   16       

I tried using calculated dimension with max month, but then i only get the FEB data, JAN excluded.         

Does anybody knows how I can get the desired outcome?

Thanks in advance!

[Anonymous]

try with total

Sum(total {<Year={$(=Max(Year))}, Month=>}[# Hours])

[rh205bip]

Hi,

Yeah, i tried that also. But then i get the same YTD for every Employee (Name), because its the total of hours, but not the total per employee.

[Anonymous]

can you try " total <Employee>" instead of only total on Expression?

[rh205bip]

Thanks for the quick replies, much appreciated!

When i add total <Employee> the table suddenly shows all months, instead of the selected month. 

[avkeep01 · Accepted solution]

Hi, 

Try aggregating over the name: 

AGGR(Sum({<Year={$(=Max(Year))}, Month={"<=$(=Max(Month))"}>}[# Hours]),Name)

Then the result will be caculated in one field, per name. 

[rh205bip]

Allright, we are getting close:D

AGGR(Sum({<Year={$(=Max(Year))}, Month={"<=$(=Max(Month))"}>}[# Hours]),Name)

Works like a charm, thanks. But now i need a second AGGR measure on #  Projecthours..

When i do the following:

=AGGR(Sum({<Year={$(=Max(Year))}, Month={"<=$(=Max(Month))"}>}[# Hours]),Name)

+ 

AGGR(Sum({<Year={$(=Max(Year))}, Month={"<=$(=Max(Month))"}>}[# Projecthours]),Name)

I dont get the expected outcome..only a few are correctly filled others not showing at all.

I know that sum X + sum Y works like this, is AGGR different in this manner?

[avkeep01]

Probably best to do it within the aggregation. 

You could try: 

=AGGR(Sum({<Year={$(=Max(Year))}, Month={"<=$(=Max(Month))"}>}[# Hours])
+ 
Sum({<Year={$(=Max(Year))}, Month={"<=$(=Max(Month))"}>}[# Projecthours]),Name)

Or even 

=AGGR(Sum({<Year={$(=Max(Year))}, Month={"<=$(=Max(Month))"}>}[# Hours]+[# Projecthours]),Name)

[rh205bip]

Great! i will use the first one, because i need to use different fields in the set analysis.