How to exclude incomplete week in calculated formulas (BM) while using fiscal calendar

renee12345
renee12345 Member
in Magic ETL

Currently am using this formula to exclude current week:

CASE
WHEN WEEK(Snapshot Date) >= WEEK(maxSnapshotDate) AND
YEAR(Snapshot Date) >= YEAR(maxSnapshotDate)
THEN 0
ELSE 1
END

however, it is excluding Sundays data. (I want to look at Fiscal calendar and it is selected under date settings)

Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓

    @renee12345 Beast mode date functions always run off of Domo's default calendar, even if the Use Fiscal Calendar box is checked on that particular card. I'd suggest creating a spreadsheet upload dataset with columns for Date, Fiscal Week, Fiscal Month, and Fiscal Year that you can join to any datasets that require fiscal calendar calculations. Then you can re-create your calculation in ETL, but using comparisons to the corresponding fiscal periods instead of the WEEK(), MONTH(), and YEAR() functions.

Answers

  • renee12345
    renee12345 Member

    Nevermind, I figured it out

  • MichelleH
    MichelleH Coach
    Answer ✓

    @renee12345 Beast mode date functions always run off of Domo's default calendar, even if the Use Fiscal Calendar box is checked on that particular card. I'd suggest creating a spreadsheet upload dataset with columns for Date, Fiscal Week, Fiscal Month, and Fiscal Year that you can join to any datasets that require fiscal calendar calculations. Then you can re-create your calculation in ETL, but using comparisons to the corresponding fiscal periods instead of the WEEK(), MONTH(), and YEAR() functions.

Categories