Forum Discussion
Remove values after current date from previous years
Ah ok that is my bad. If you want the visual to show the dates but with empty values, then you can use this measure (forget the calculated table idea but you do need to add the MonthDay column). The measure would look something like this (this is untested and typed without intellisense so forgive any typo's);
Measure =
VAR maxCurrentYear = MAXX(FILTER(Table, Table[AcademicYear] = "2019-2020"), Table[MonthDay])
RETURN
IF(HASONEVALUE(Table[Date]) && AVERAGE(Table[MonthDay]) > maxCurrentYear,
BLANK(),
SUM(<ValueColumn>)
)This will sum a column if the context this measure is calculated in has a MonthDay lower than maxCurrentYear (which is the current year max monthday).
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
JarroVGIT thanks for the prompt reply. For the date fields in the calcuation, do I have to use the Calendar table? Right now I am, and I am getting the error message below. What do you think is going on?
JarroVGIT , MonthDay is being calculated the way you suggested. See below.
- In the MAXX statement, you want to get the MonthDay from the ApplicationCount table.
Thanks so much. Still not working, but we are getting close.
This is the calculated field in effect:
Measure = VAR maxCurrentYear = MAXX(FILTER(application_count, application_count[academic year] = "2019-2020"), application_count[MonthDay]) RETURN IF(HASONEVALUE('Dates Adj'[Date]) && AVERAGE('Dates Adj'[MonthDay]) > maxCurrentYear, BLANK(), application_count[Total Apps])What column from which table do you use for the Matrix columns? (That should be 'Dates Adj'[Date])