Forum Discussion
YTD Change
Hi, I have a table like below:- Current Balance Date Deal Price Value 34 12/14/2022 A 70 3 23 12/14/2022 A 60 2 23 12/14/2022 A 50 58 23 12/14/2022...
Anonymous This is essentially MTBF. Try this:
Column = VAR __Deal = [Deal] VAR __Price = [Price] VAR __FirstDate = MINX(FILTER('Table', [Deal] = __Deal && [Price] = __Price), [Date]) VAR __LastDate = MAXX(FILTER('Table', [Deal] = __Deal && [Price] = __Price), [Date]) VAR __FirstValue = MINX(FILTER('Table', [Deal] = __Deal && [Price] = __Price && [Date] = __FirstDate), [Value]) VAR __LastValue = MINX(FILTER('Table', [Deal] = __Deal && [Price] = __Price && [Date] = __LastDate), [Value]) VAR __Result = __LastValue - __FirstValue RETURN __ResultAnonymous in this case,
first create a calc column in the orginal table
Deal_Date = 'Table'[Deal]&"|"&'Table'[Date]then you can create another calc table from the main table like below
then create below colums in the new table
Deal_Min_Date = Min_Max_value_2[Deal] & "|" & Min_Max_value_2[Min Date]Deal_Max_Date = Min_Max_value_2[Deal] & "|" & Min_Max_value_2[max Date]Min_Date_Value = LOOKUPVALUE('Table'[Value],'Table'[Deal_Date],Min_Max_value_2[Deal_Min_Date])Max_Date_Value = LOOKUPVALUE('Table'[Value],'Table'[Deal_Date],Min_Max_value_2[Deal_Max_Date])Max-Min = Min_Max_value_2[Max_Date_Value]-Min_Max_value_2[Min_Date_Value]please note that you need all above columns, you can combine them in one dax column to have a clean view. just for illustration purpose, i have added all columnsalso attached pbix file.
Anonymous This is essentially MTBF. Try this:
Column =
VAR __Deal = [Deal]
VAR __Price = [Price]
VAR __FirstDate = MINX(FILTER('Table', [Deal] = __Deal && [Price] = __Price), [Date])
VAR __LastDate = MAXX(FILTER('Table', [Deal] = __Deal && [Price] = __Price), [Date])
VAR __FirstValue = MINX(FILTER('Table', [Deal] = __Deal && [Price] = __Price && [Date] = __FirstDate), [Value])
VAR __LastValue = MINX(FILTER('Table', [Deal] = __Deal && [Price] = __Price && [Date] = __LastDate), [Value])
VAR __Result = __LastValue - __FirstValue
RETURN
__ResultThanks, this was perfect!