How to set up a Future Date Filter

jamesdII

I'm trying to figure out how to set up a beast mode that will allow me to filter out historical dates while keeping the Date Range open to Current FY. I feel like I almost had it with the below calculation, but it's cutting off anything that is scheduled after this current week time period in future months:

 

case when year(current_date) <= year(`Target Date`) and week(CURRENT_DATE) <= week(`Target Date`) then 'Future' else 'Historical' end

 

I tried to replace 'week' with 'day' and then I'm only getting the last couple of days of the month.

 

Essentially, if I wanted it to work properly, I need it to show me data for every day after today, until the end of our fiscal year.

 

Not sure what I'm doing wrong, probably something obvious I'm just overlooking, but any help is appreciated!

GrantSmith

Hi @jamesdII,

 

Have you tried just comparing the dates themselves?

 

CASE WHEN current_date <= `Target Date` then 'Future' else 'Historical' END

This treats a target date of today as in the future. (simply change <= to < to exclude today from the future group.

 

The other issue with your current logic is that both conditions must be true so for example if you had a date from January of next year  it wouldn't consider it as a future date because the week check would fail even though the year is after the current year.

jaeW_at_Onyx

Clarifying question. Do you actually have future dated data?

 

You could do something like date_format( `target_date`, '%Y-%m') to group your dates by yearMonth number and then take the count of each row, just to verify that you have future dated data.

 

Assuming you do, you can follow @GrantSmith 's tip with a small twist

 

If you only want data from the current fiscal year (assuming your fiscal calendar runs on a calendar year) then fix the pseudo-code, but this should work.

I used yearCurrent = yearTarget b/c you want THIS fiscal Year not all future dates.

 

case when year(current_date) = year(`Target Date`)
and CURRENT_DATE <= `Target Date` then 'FY Future'

WHEN year(current_date) < year(TargetDate) then 'Future'
 else 'Historical' end 

If your fiscal year does not align with the calendar year, I advise you look into using a Date Dimension table.  The beast mode date functions (even with the offset calendar feature) will ALWAYS assume a calendar year.

 

take a look at

https://knowledge.domo.com/Visualize/Adding_Cards_to_Domo/KPI_Cards/Transforming_Data_Using_Beast_Mode/Sample_Beast_Mode_Calculations%3A_Date_Transforms