Forum Discussion
Handling 'Select all' with YTD Measure
Hey All,
I'm having a little bit of an issue with a custom measure that is calculating YTD from MTD data. I have a monthly slicer that allows me to easily select YTD values at each month. I have no issues with being able to set a range in my calculation based upon the selected months. However, when "Select all" is selected, my measure breaks as I am unsure of how to handle this selection. Here is my measure:
Total Revenue YTD =
VAR MinDate = DATE(YEAR(MAX('DateTable'[DATE])), 1, 1)
VAR MaxDate = SELECTEDVALUE('DateTable'[DATE])
RETURN
CALCULATE(
SUM('RevenueTable'[Revenue]), FILTER('RevenueTable', 'RevenueTable'[Date] >= MinDate && 'RevenueTable'<= MaxDate)
)
Hi Anonymous ,
Your issue with the measure is your max date, since you are doing the SELECTEDVALUE this only returns a single value when you select more than one your metric breaks because for this specific calculation you get a table of multiple values so the value returned is blank.
Change the SELECTEDVALUE for MAX and this should work for all selections.
Total Revenue YTD = VAR MinDate = DATE(YEAR(MAX('DateTable'[DATE])), 1, 1) VAR MaxDate = MAX('DateTable'[DATE]) RETURN CALCULATE( SUM('RevenueTable'[Revenue]), FILTER('RevenueTable', 'RevenueTable'[Date] >= MinDate && 'RevenueTable'<= MaxDate) )
1 Reply
- MFelix
Super User
Hi Anonymous ,
Your issue with the measure is your max date, since you are doing the SELECTEDVALUE this only returns a single value when you select more than one your metric breaks because for this specific calculation you get a table of multiple values so the value returned is blank.
Change the SELECTEDVALUE for MAX and this should work for all selections.
Total Revenue YTD = VAR MinDate = DATE(YEAR(MAX('DateTable'[DATE])), 1, 1) VAR MaxDate = MAX('DateTable'[DATE]) RETURN CALCULATE( SUM('RevenueTable'[Revenue]), FILTER('RevenueTable', 'RevenueTable'[Date] >= MinDate && 'RevenueTable'<= MaxDate) )