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

[Anonymous]

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

[PC2790]

Hey @Anonymous ,

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

[Anonymous]

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 · Accepted solution]

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]

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!