Fiscal Year MTD and YTD Calculation Urgent Help!

Hi Everyone,

I need your help for Fiscal Year calculations. I am trying to achieve sales numbers for Fiscal Year MTD and Fiscal Year YTD.

Where MTD means Month to Yesterday and YTD means Year to Yesterday. Our Fiscal Year is from May to April (Eg: FY 2020 is from May 2019 to April 2020)

The data which i am using is InvDate.

What I've done till now-

InvDate FY-

DATEADD('month', 8, [InvDate])

Today FY-

DATEADD('month', 8, TODAY())

MTD-

ZN(SUM( IF YEAR([InvDate FY]) = YEAR([Today FY])

AND MONTH([InvDate FY]) = MONTH([Today FY])

AND DAY([InvDate FY]) <= DAY([Today FY]) - 1

THEN [Sales]

END

))

YTD-

ZN(SUM(IF [InvDate FY] <= [Today FY] - 1 AND

DATETRUNC( "year", [InvDate FY]) = DATETRUNC("year", [Today FY] )

THEN [Sales]

END))

Till Yesterday (i.e., 31st March 2020 the data was correct but today 1st April all MTD columns is displaying 0)

Please help!

Thank.