cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## 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 10
Resident Rockstar

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! 🙂

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helper IV

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?

Resident Rockstar

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! 🙂

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helper IV

@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?

Resident Rockstar
In the MAXX statement, you want to get the MonthDay from the ApplicationCount table.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helper IV

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])``````
Resident Rockstar

What column from which table do you use for the Matrix columns? (That should be 'Dates Adj'[Date])

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Resident Rockstar

Otherwise, feel free to PM me a link to your PBIX and I will have a closer look for you 🙂

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helper IV

@JarroVGIT ,  MonthDay is being calculated the way you suggested. See below.

Community Champion

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

do not hesitate to give a kudo to useful posts and mark solutions as solution

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.