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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Using DirectQuery/Transform data to pull in records of only current and last year

Hi all,

as my title suggests, im pulling in a sql table that is going to be used as my calendar for my data. however the table has calendar data from 2008 to 2026, i only want to pull in current and last years data. i also want this to be dynmaic so i cant hardcode the years into the query.

i created some measures for current & previous year but then found out measures cant be used in import query window.

does anyone know of a simple way in powerbi to do this? my table has a simple years column that i want to filter to only pull in current and previous year.

or do i have to build the query manually in sql and put it into the "SQL statement" window when setting up my connection?

 

many thanks

1 ACCEPTED SOLUTION
PC2790
Community Champion
Community Champion

As per my knowledge there are two ways:

1) Extract only the required data from SQL using the script window of power bi. I may not be expert in SQL but something like this script

SELECT ... FROM ... WHERE YEAR(DATE) = YEAR(GETDATE()) - 1 OR YEAR(DATE) > YEAR(GETDATE())
 2. And the second option is to do the selection using DAX and I suppose you are not looking for that and may not be the best solution consdering performance.

 

View solution in original post

4 REPLIES 4
PC2790
Community Champion
Community Champion

Hey @Anonymous ,

 

See a similar thread that might give you some insights about the date table in direct query.

 

 

Anonymous
Not applicable

hi @PC2790  thanks for your reply, i took a look at that thread and it seems that is referring to creating a date table in power bi? 

i have my date table already set up, i was just wondering was there a different way in powerbi to pull in the current and previous years worth of data other than just doing it in SQL and putting in the query in the sql staement window? as i dont want powerbi to read in all the data in my date table and then filter it to current and previous year.

thank you

PC2790
Community Champion
Community Champion

As per my knowledge there are two ways:

1) Extract only the required data from SQL using the script window of power bi. I may not be expert in SQL but something like this script

SELECT ... FROM ... WHERE YEAR(DATE) = YEAR(GETDATE()) - 1 OR YEAR(DATE) > YEAR(GETDATE())
 2. And the second option is to do the selection using DAX and I suppose you are not looking for that and may not be the best solution consdering performance.

 

Anonymous
Not applicable

thanks @PC2790 . yes i was just wondering was there another way using powerbi than the sql window. doesnt look like it.

will go with sql as you suggested. 

thanks! 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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