MTD Average Issue not capturing final day

Question

I'm running a MTD average formula after this section. Our financial data is always for the previous day, so on February 1st it should be capturing the final day of January. However, with this set up it is catching February (current month) data once the month changes and not the previous day information of January 31st.  I need to be able to capture the final day to finish the monthly financial data, but then work on the current month on the 2nd of month and not continue working in the previous month.

My guess is that I need another filter or formula for a IF current month is yyyy/mm/01 then - 1 day, else use current date. Any suggestions would be appreciated.

alexnajm · Accepted Answer

Hi @adam_jones,

Attached is my solution below. In summary, the top container tests as if the "DateTimeNow" is 2022-02-01 and it will calculate the month value of your [Date] data, the month value of the Previous Day from "DateTimeNow", and filter to when the two month values equal each other. The top container should act as if you ran this on 2/1 and will give you January data.

The second container is what I would implement in your workflow, using the actual DateTimeNow() function rather than the placeholder 2022-02-01 value. So if you run it today, you should get only February data.

Let me know if you have any questions, and good luck solving!

-Alex Abi-Najm