TotalYTD is not working as I expected

[tchen0]

I have been trying to get TotalYTD work.   See the data and formula below :

  1) For YTD Act, YTD_Fcst and YTD Var, TotalYTD is working as expected.

  2) For YTD Var Abs, TotalYTD is not working as expected.  I can not figure out why only this one is not working properly.

Thanks

							???
				=totalYTD('OP - Data'[Actual], 'Date Key'[Date_key])	=totalYTD('OP - Data'[Fcst], 'Date Key'[Date_key])	=totalYTD( 'OP - Data'[Var], 'Date Key'[Date_key])	=totalYTD ('OP - Data'[Var Abs],  'Date key'[Date_key])
Actual = CALCULATE(sum('OP - Data'[Fcst_Act]),  filter('OP - Data', 'OP - Data'[Fcst_N]=0 && 'OP - Data'[Fcst Type]="Actual"))	Fcst = CALCULATE(sum('OP - Data'[Fcst_Act]), filter('OP - Data','OP - Data'[Fcst_N]=4 && 'OP - Data'[Fcst Type]="Fcst" ))	New Measure	New Measure	working	working	working	not working	Need to be
Actual	Fcst	Var  (= Actual - Fcst)	Var Abs (= abs(Actual - Fcst)  )	YTD Act	YTD_Fcst	YTD Var	YTD Var Abs	YTD Var Abs
726,247	467,896	258,351	258,351	726,247	467,896	258,351	258,351	258,351
653,244	572,124	81,120	81,120	1,379,491	1,040,020	339,471	339,471	339,471
513,018	606,354	-93,336	93,336	1,892,509	1,646,374	246,135	246,135	432,807
367,854	629,864	-262,010	262,010	2,260,363	2,276,238	-15,875	15,875	694,817

[Ashish_Mathur]

Hi,

Aren't your last two numbers in the last column incorrect.  The second last row's answer should remain 246,135 and the last row's answer should be 15,875.  How can the answer be 694,817?

[tchen0]

They are correrct. 

694,817 = 258,351+81,120+93,336+262,010

Please refer to the fourth column. Thanks. 

[Ashish_Mathur]

Hi,

Your picture is not clear.  Is your last row a Grand Total row.  Share the link from where i can download your PBI workbook and show the table where you want the result to appear.

[tchen0]

See the example I created:

https://1drv.ms/u/s!AowzPecIR5bZhiJTAAlG0svwbiA5

The column "YTD Var Abs" is not calculating right. 

Any thoughts? Thanks. 

[Ashish_Mathur]

Hi,

I believe this is the answer you are expecting.  This is my measure

=YTD Var Abs = if(HASONEVALUE('Date'[Date_key].[Month]),if(max('Date'[Date_key])<now(), totalYTD('Data'[Var Abs],'Date'[Date_key]),blank()),SUMX(SUMMARIZE(VALUES('Date'[Date_key].[Month]),[Date_key].[Month],"ABCD",if(max('Date'[Date_key])<now(), totalYTD('Data'[Var Abs],'Date'[Date_key]),blank())),[ABCD]))

[tchen0]

Thank you for your reply.  It does not get what I expect.  I want "YTD Var Abs" to be calcuated based on the column - "Var Abs".  

2018					not working
Month	Actual	Fcst	Var	Var Abs	YTD Var	YTD Var Abs ( incorrect)	YTD Var Abs I expect to be
1	5	6	-1	1	-1	1	1=1
2	6	5	1	1	0	0	2 =(1+1)
3	4	8	-4	4	-4	4	6 =(1+1+4 )
4	10	10	0	0	-4	4	6 =(1+1+4+0)
5	9	8	1	1	-3	3	7 =(1+1+4+0+1)
6
7
8
9
10
11
12

[Ashish_Mathur · Accepted solution]

Hi,

In your Matrix visual, i dragged Month from the Date Table to the Rows section.  I wrote the folloing measure

YTD Var Abs = if(ISBLANK([Var Abs]),BLANK(),SUMX(CALCULATETABLE(SUMMARIZE(ALL('Date'[Month]),'Date'[Month],"ABCD",[Var Abs]),DATESBETWEEN('Date'[Date_key],DATE(YEAR(MAX('Date'[Date_key])),1,1),MAX('Date'[Date_key]))),[ABCD]))

Hope this helps.

[tchen0]

Thanks. It works!!

[Ashish_Mathur]

You are welcome.

[tchen0]

Also see the problem below: 

	2018					not working
Month	Actual	Fcst	Var	Var Abs	YTD Var	YTD Var Abs	YTD Var Abs I expect to be
1	5	6	-1	1	-1	1	1=1
2	6	5	1	1	0	0	2 =(1+1)
3	4	8	-4	4	-4	4	6 =(1+1+4 )
4	10	10	0	0	-4	4	6 =(1+1+4+0)
5	9	8	1	1	-3	3	7 =(1+1+4+0+1)
6
7
8
9
10
11
12

[gooranga1]

Not entirely sure about this as I haven't actually replicated it but have you tried changing your "Var abs" measure to something like 

=(Actual-Fcst)*if( (Actual-Fcst) <0,-1,1)

?

[tchen0]

Unfortunately, it is not working either. For example, I want to calculate YTD ( Jan to Mar ). The correct answer is

Abs( Actual1 - Fcst1)  + Abs (Actual 2 - Fcst 2 ) + Abs(Actual 3 - Fcst3)  

But it results in the followng incorrect answer:

Abs [  (Actual1 +Actual2 + Actual 3)   -   (Fcst1+Fcst2+Fcst3)  ]

Any thoughts ?  Thanks.