How to filter initial data pull using today's date as a reference for another column

[galpic2]

Hi all,

Very new to Power BI and I've been searching the internet for help on this issue and haven't had much luck. I've got a very basic query below and just cannot figure out how to get this code to do what I need it to do.

I have days in 1 column and fiscal year in another. Right now I've got the days column to only show data up until yesterday, all good there. But I also want to filter the fiscal year to only show the last 2 Fiscal Years (based on the fiscal year result of yesterday) - 2. In this case the current fiscal year is 2025 so I just manually set FIS_YR_ID >= 2023

So I'm trying to figure out how to fix this code so instead of just manually entering 2023, it calculates what that value should be based on yesterday's date.

SELECT *

FROM
TABLE

WHERE
FIS_YR_ID >= 2023
AND DAY <= (select dateadd(DAY, -1, getdate()))

[SamWiseOwl]

Hi @galpic2 

This looks like SQL to me:
Year(getdate()-1) + IIF(month(getdate()-1) >= 4, 1,0) -2

Return the Year of yesterday then if the month is april onwards add 1 year otherwise add nothing.

Reduce this calculated FY by 2 years.

[galpic2]

Unfortunately our fiscal year doesn't align with calendar dates, so one year the end of the fiscal year could be on April 30, the next year it could be on April 24, so just taking today's date and subtracting x number of days/months/years won't always give me the correct answer and would lead to pulling or excluding data that I don't want to pull or exclude.

[vanessafvg · Accepted solution]

sql doesn't use iif statements for the record

your where clause could look something like this.

assuming you can do a cte (common table expression) in snowflake

with cte_fiscal as (
select
max(FIS_YR_ID) as max_year
from
tablename
where
day = dateadd('day', -1, currentdate())
)
select
*
from
tablename
cross join cte_fiscal cte
where
FIS_YR_ID >= max_year -2
and FIS_YR_ID <= max_year

[galpic2]

This worked!! Thank you!!

The only thing I had to change was 

'day = dateadd('day', -1, currentdate())'

it didn't recognize 'currentdate()' so I just had to change it to 'getdate()' and it worked perfectly!

[SamWiseOwl]

Good to know, thank you @vanessafvg! I couldn't tell which brand of SQL they were writing, since this is a Microsoft Forum I assumed T-SQL which does support IIF. 

[vanessafvg]

interestingly in later version it does seem to have this syntax available, so my bad, however i see it still converts it to a case statement anway

https://learn.microsoft.com/en-us/sql/t-sql/functions/logical-functions-iif-transact-sql?view=sql-se...

[vanessafvg]

i have never written an iif statement in sql, and ive done sql development for over 25 years 🙂  sql uses a case statement for if.

[SamWiseOwl]

Ahh so Case is better performance than IIF ? That is good to know thank you! @vanessafvg Always happy for advice from the masters.

One that blew my mind was dragging the columns in the results pane, when was that added 🤔😂

[vanessafvg]

where are you trying to do this exactly? how are you ingesting this data, what is your source?  It is always preferable t provide sample data with your expected output.

[galpic2]

I'm pulling from a snowflake table and the data is more or less laid out like below, pretty basic, DAYS in one column, corresponding FIS_YR_ID in another.

The original source data has data going back decades and looking forward decades but I only need to pull in the last 2 years of fiscal dates. I'm able to easily say I only want Days less than today so I don't pull in all of the future data, but I'm trying to figure out how to tell SQL to filter FIS_YR_ID by looking up today's FIS_YR_ID (2025) and just subtracting 2 (2023) within the initial query.

DAY	FIS_YR_ID
8/5/2024	2025
8/4/2024	2025
....	....
8/4/2023	2024
...	...
8/4/2022	2023
...	...