Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
The goal is to remove values after current date (most recent in this sample) from previous years for comparison purposes. Any insights?
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! 🙂
Proud to be a Super User!
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! 🙂
Proud to be a Super User!
@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?
Proud to be a Super User!
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])
Proud to be a Super User!
Otherwise, feel free to PM me a link to your PBIX and I will have a closer look for you 🙂
Proud to be a Super User!
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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |