Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
diogobraga2
Helper IV
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?

2019-12-20_2218.png

10 REPLIES 10
JarroVGIT
Resident Rockstar
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!




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? 

2019-12-21_1632.png

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!




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

 

Screen Shot 2019-12-23 at 8.35.22 PM.png

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!




Thanks so much. Still not working, but we are getting close.

2019-12-24_0911.png

 

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])





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

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 🙂





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

Proud to be a Super User!




@JarroVGIT ,  MonthDay is being calculated the way you suggested. See below.2019-12-23_2039.png

az38
Community Champion
Community Champion

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

LinkedIn


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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.