YTD Measure Using Slicer Selection

[mrfsca]

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

% Target YTD =

var sg = SELECTEDVALUE('Table1'[Type])

var ds = SELECTEDVALUE('Table1'[Group])

Var _maxdata = CALCULATE(MAX('TableDate'[Date]), FILTER('Table1', 'Table1'[Type]=sg && 'Table1'[Group]=ds))

RETURN

CALCULATE(DIVIDE([Target YTD], _maxdata))

This is my dummy data

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%

[Ashish_Mathur]

Hi,

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

[Irwan]

hello @mrfsca 

please check if this accomodate your need.

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])
Return
SUMX(
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Type]=_Type&&
        'Table'[Group]=_Group&&
        'Table'[Index]<=_Index
    ),
    'Table'[ order ]
)

TGT Accum = 
var _Type = SELECTEDVALUE('Table'[Type])
var _Group = SELECTEDVALUE('Table'[Group])
var _Index = SELECTEDVALUE('Table'[Index])
Return
SUMX(
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Type]=_Type&&
        'Table'[Group]=_Group&&
        'Table'[Index]<=_Index
    ),
    'Table'[Target']
)

Here is the DAX for percent Order YTD.

Order YTD = 
DIVIDE(
    [Order Accum],
    MAXX(
        ALLSELECTED('Table'),
        [Order Accum]
    )
)

for TGT YTD, the DAX is exactly same but different expression (use "target" instead of "Order")

TGT YTD = 
DIVIDE(
    [TGT Accum],
    MAXX(
        ALLSELECTED('Table'),
        [TGT Accum]
    )
)

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.

Hope this will help.

Thank you.