A calendar pick to recover historical data

[poko]

Hello everyone. 

I am a beginner and need a help with following calendar :

Table = CALENDAR(MIN(Purchaset[DateOPurchase), MAX(Purchase[DateOfPurchase]))

and date calculation with IF criteria...

I need data from a SQL server, 5 years older and more to a date (the date you pick in the calendar) . Basically when I click on a date in calendar I need to bring data from a database more than 5 years old to the picked date in the calendar and must meet IF criteria status "NEW" in columns "From" and "To"  .   

Every help is welcome.

[grazitti_sapna]

Hi @poko,

In order to filter data in Power BI to display records older than 5 years from a selected date in the calendar, while ensuring that the status in both the "From" and "To" columns is "NEW", please follow the steps below:
Create a Calendar Table: Modify the calendar to cover a range that includes dates older than 5 years. You can achieve this using the CALENDAR function as follows:
CalendarTable = CALENDAR(DATEADD(TODAY(), -5, YEAR), TODAY())

Filter Data from SQL Server: To retrieve the relevant data, apply a filter that checks for records older than 5 years from the selected date in the calendar. Additionally, the data should meet the criteria where the "From" and "To" columns have the status "NEW." Assuming the data is being accessed via DirectQuery, you can create a calculated table or measure as follows:
FilteredData =
CALCULATETABLE(
Purchase,
DATEDIFF(Purchase[DateOfPurchase], SELECTEDVALUE(Calendar[Date]), YEAR) >= 5,
Purchase[From] = "NEW",
Purchase[To] = "NEW"
)

Add a Calendar Slicer: Add the CalendarTable to your report as a slicer, allowing users to select a date. This will dynamically filter the FilteredData table based on the chosen date.
I hope this will help you
Thank you.

[poko]

when I fit 

FilteredData =
CALCULATETABLE(
Purchase,
DATEDIFF(Purchase[DateOfPurchase], SELECTEDVALUE(Calendar[Date]), YEAR) >= 5,
Purchase[From] = "NEW",
Purchase[To] = "NEW"
)

always sayr error...The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

[Kedar_Pande]

@poko 

Store the selected date from the slicer

SelectedDate = SELECTEDVALUE(Calendar[Date])

Create a new table

FilteredData = 
CALCULATETABLE(
Purchase, 
Purchase[DateOfPurchase] <= EDATE(SelectedDate, -60), // 5 years (60 months) older than selected date
Purchase[From] = "NEW",
Purchase[To] = "NEW"
)

You can now use the FilteredData table for any visualizations, or further calculations in Power BI.

If this helped, a Kudos 👍 or Solution mark would be great!
Cheers,
Kedar Pande
www.linkedin.com/in/kedar-pande

[dharmendars007]

Hello @poko , 

You can create a date slicer to select the date like the below 

SelectedDate = SELECTEDVALUE(Calendar[Date])

And now create a FIlter condition which you can use to filter the table

DateThreshold = DATE(YEAR([SelectedDate]) - 5, MONTH([SelectedDate]), DAY([SelectedDate]))

In power BI now you can fetch the table based on the criteria 

FilteredData =
CALCULATETABLE(SQLData, SQLData[Date] <= [DateThreshold], SQLData[From] = "NEW",SQLData[To] = "NEW")

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[poko]

Hello,

same here, I have this problem "A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed." plase can you help?