Trying to pull data from Current Year and Year ago using Date Column

Question

Hello There,I have product images of ads at a YYYY-MM-DD level, and I am trying to use the date column filter to select a week such as "6/11/2023" but then have the option of filtering between "Current Year" and "Year Ago".Product ImageDatehttp://productimage1.com6/11/2023http://productimage2.com6/11/2023http://productimage3.com6/12/2022http://productimage4.com6/12/2022http://productimage5.com6/12/2022&nbsp;The purpose of this is to have 2 image grids. The top will show the "Product Image" from the Ads for the current year filtered to date "6/11/2023" and the bottom image grid will show the "Product Images" from the same week year ago.Here is the same link to a sample of my excel data. I am stumped! Any help is appreciated!Power BI Using Current Year and Year Ago.xlsx

anonymous · Accepted Answer

Hi&nbsp;UKFan12&nbsp;,
&nbsp;
Here's my solution.
You can create a calendar table for filtering date and create two measures for filtering images.
Calendar Table:
Calendar = ADDCOLUMNS(CALENDAR(DATE(2023,6,1),DATE(2023,6,30)),"WeekNum",WEEKNUM([Date],2),"Year",YEAR([Date]))
Note that there's no relationship between tables.

&nbsp;
Two measures:
Current Year Filter = IF(MAX('Table'[Date])=SELECTEDVALUE('Calendar'[Date]),1)Year Ago Filter = 
var _day1=MAX('Table'[Date])
var _weeknum1=WEEKNUM(_day1,2)
var _year1=YEAR(_day1)
var _day2=MAX('Calendar'[Date])
var _weeknum2=WEEKNUM(_day2,2)
var _year2=YEAR(_day2)
return IF(_weeknum1=_weeknum2&amp;&amp;_year1=_year2-1,1)
Put Current Year Filter measure into visual-level filter of the visual called Current Year and put Year Ago Filter measure into visual-level filter of the visual called Year Ago. Both set up show items when the value is 1.

Create a slicer with dates from the calendar. When you filter date such as&nbsp;"6/11/2023", below is the result.

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Best Regards,
Stephen Tao
&nbsp;
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly. &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;

Forum Discussion

Trying to pull data from Current Year and Year ago using Date Column

2 Replies

Related content

Trying to sort column headers

Trying to understand refresh memory usage

Trying to expand a json array into multiple columns

Trying to get Max value using a column parameter

Trying to Extract Town name

Product Image	Date
http://productimage1.com	6/11/2023
http://productimage2.com	6/11/2023
http://productimage3.com	6/12/2022
http://productimage4.com	6/12/2022
http://productimage5.com	6/12/2022