Forum Discussion
YTD Change
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!
Greg_Deckler , one question:
To calculate variable _First Value, you are using a min function on the filtered table.
If I have data for both 2021 and 2022, would not this variable pick 2021 value instead of first date of 2022 to calculate YTD?
Hi, I have a table like below:
Date Sector Value 1/7/2015 A 44 1/14/2015 A 56 1/21/2025 A 7 1/28/2015 A 8 1/7/2015 B 3 1/14/2015 B 4 1/21/2025 B 67 1/28/2015 B 54 1/7/2015 C 66 1/14/2015 C 43 1/21/2025 C 2 1/28/2015 C 1 I want to create a summary table where I show the WoW change and MoM change from the latest date for every sector. I am having a hard time writing the correct DAX code to calculate both of these metrics. Please help.
Thanks,