How to set up a Future Date Filter

jamesdII
jamesdII Member
in Magic ETL

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!

Comments

  • GrantSmith
    GrantSmith Coach

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories