Forum Discussion
How to filter initial data pull using today's date as a reference for another column
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()))
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
10 Replies
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.
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
... ...
Hi galpic2
This looks like SQL to me:
Year(getdate()-1) + IIF(month(getdate()-1) >= 4, 1,0) -2Return the Year of yesterday then if the month is april onwards add 1 year otherwise add nothing.
Reduce this calculated FY by 2 years.
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.
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
