YTD Measure Using Slicer Selection

Question

Hi, I am doing a simple formula in excel where i wanna calculate order qty and target (both YTD) to put in a column and line chart. When I'm transforming it into PBI, I made 2 quick measure using TOTALYTD to calculate the tgt accum and order accum but when I create a measure to calculate the order or target YTD, i can't seem to figure it out how to call the max value like i did in my excel. I put 2 slicer in my report so i need to show the data according to the selected value. This following syntax doesnt work. Please advise me. Thank you in advance&nbsp;% Target YTD = var sg = SELECTEDVALUE('Table1'[Type])var ds = SELECTEDVALUE('Table1'[Group])Var _maxdata = CALCULATE(MAX('TableDate'[Date]), FILTER('Table1', 'Table1'[Type]=sg &amp;&amp; 'Table1'[Group]=ds))RETURNCALCULATE(DIVIDE([Target YTD], _maxdata))This is my dummy dataTarget'&nbsp;Tgt Accum&nbsp;&nbsp;order&nbsp;&nbsp;Order Accum&nbsp;&nbsp;order ytd&nbsp;&nbsp;tgt ytd&nbsp;200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 195&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1958.13%8.33%200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 400&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 161&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 35614.83%16.67%200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 600&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 130&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 48620.25%25.00%200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 800&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 132&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 61825.75%33.33%200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 187&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 80533.54%41.67%200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 168&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 97340.54%50.00%200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,400&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 166&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,13947.46%58.33%200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,600&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 167&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,30654.42%66.67%200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,800&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 163&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,46961.21%75.00%200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 160&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,62967.88%83.33%200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 179&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,80875.33%91.67%200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,400&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 189&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,99783.21%100.00%

irwan · Answer

hello&nbsp;mrfsca&nbsp;&nbsp;please check if this accomodate your need.&nbsp;i assumed you good with accumulated value. but just incase, here are the DAX for both accumulated value.Order Accum = var _Type = SELECTEDVALUE('Table'[Type])var _Group = SELECTEDVALUE('Table'[Group])var _Index = SELECTEDVALUE('Table'[Index])ReturnSUMX(&nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; ALLSELECTED('Table'),&nbsp; &nbsp; &nbsp; &nbsp; 'Table'[Type]=_Type&amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; 'Table'[Group]=_Group&amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; 'Table'[Index]&lt;=_Index&nbsp; &nbsp; ),&nbsp; &nbsp; 'Table'[ order ])TGT Accum = var _Type = SELECTEDVALUE('Table'[Type])var _Group = SELECTEDVALUE('Table'[Group])var _Index = SELECTEDVALUE('Table'[Index])ReturnSUMX(&nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; ALLSELECTED('Table'),&nbsp; &nbsp; &nbsp; &nbsp; 'Table'[Type]=_Type&amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; 'Table'[Group]=_Group&amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; 'Table'[Index]&lt;=_Index&nbsp; &nbsp; ),&nbsp; &nbsp; 'Table'[Target'])&nbsp;Here is the DAX for percent Order YTD.Order YTD = DIVIDE(&nbsp; &nbsp; [Order Accum],&nbsp; &nbsp; MAXX(&nbsp; &nbsp; &nbsp; &nbsp; ALLSELECTED('Table'),&nbsp; &nbsp; &nbsp; &nbsp; [Order Accum]&nbsp; &nbsp; ))for TGT YTD, the DAX is exactly same but different expression (use "target" instead of "Order")TGT YTD = DIVIDE(&nbsp; &nbsp; [TGT Accum],&nbsp; &nbsp; MAXX(&nbsp; &nbsp; &nbsp; &nbsp; ALLSELECTED('Table'),&nbsp; &nbsp; &nbsp; &nbsp; [TGT Accum]&nbsp; &nbsp; ))&nbsp;also, dont mind the index column, i used it for ordering but i assumed you have another column for indexing since you have done the accumulated value calculation.&nbsp;Hope this will help.Thank you.

ashish_mathur · Answer

Hi,
I cannot understand your requirement.&nbsp; Without a Date column, how can you complete YTD figures?&nbsp; Review your data very well, repost something more meaningful and show the expected result.

Forum Discussion

YTD Measure Using Slicer Selection

2 Replies

Related content

Selected slicer

Slicer default selection

Single select slicer

Between date slicer - calender selection

DAX Measure ignore filter/slicer selection

Target'	Tgt Accum	order	Order Accum	order ytd	tgt ytd
200	200	195	195	8.13%	8.33%
200	400	161	356	14.83%	16.67%
200	600	130	486	20.25%	25.00%
200	800	132	618	25.75%	33.33%
200	1,000	187	805	33.54%	41.67%
200	1,200	168	973	40.54%	50.00%
200	1,400	166	1,139	47.46%	58.33%
200	1,600	167	1,306	54.42%	66.67%
200	1,800	163	1,469	61.21%	75.00%
200	2,000	160	1,629	67.88%	83.33%
200	2,200	179	1,808	75.33%	91.67%
200	2,400	189	1,997	83.21%	100.00%