Forum Discussion
Remove values after current date from previous years
The goal is to remove values after current date (most recent in this sample) from previous years for comparison purposes. Any insights?
10 Replies
Hi diogobraga2
if i understand you correct you can create a new simple table
Table = FILTER( ALL('Table 1'); 'Table1'[Date]<=today() )do not hesitate to give a kudo to useful posts and mark solutions as solution
You have a Date column in your table, and we are going to need to extract the MonthDay value from that (like 131 for Jan 31st and 506 for May 6th, 1211 for December 11th etc). Then you could create a filtered context where all other dates for the previous years are removed. First let's add the MonthDay column as a calculated column to your table following this post of my in another topic.
Now, we are going to add a calculated table (just as example. the point is that you can create a filtered context with this new column)
Table = VAR lastDateCurYear = CALCULATE(MAX(Table[Date]), FILTER(Table, Table[AcademicYear] = "2019-2020")) VAR curMonthDay = CALCULATE(MAX(Table[MonthDay]), FILTER(Table, Table[Date] = lastDateCurYear)) RETURN FILTER(Table, Table[MonthDay] <= curMonthDay)If you have questions, let me know! The topic I linked to above is about the same issue 🙂
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! 🙂
Thanks for your reply. I applied your solution, but this is not the result I was looking for. In the screenshot I provided in the original question, the goal is to keep all dates, but clear the values, which are application counts, for dates inside the green square. It looks like we can still leverage some of you calcuation, any insights on how to tweak it to get the desired result?
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! 🙂
