Need help: Issue with MTD sales (measure) using fiscal year calendar

[AmeenVanakar]

Hi,

I am trying to arrive at the MTD, previous month & QTD sales using a measure but I always get blanks. However I am able to arrive at the previous year same period data as shown in the screenshot below. Can you help me with this please?

Note: I am using a fiscal year calendar (April - March).

Here is the link to access .pbix file 

https://1drv.ms/u/c/d89d89d28c3ea2f6/EbpOudt5bdxOjGNPYh8malUBK-vgJQDHR1ZBREu5Yx9DHA?e=RGHWyg 

[danextian · Accepted solution]

Hi @AmeenVanakar 

MTD and QTD will not return because your Dates table is until December 2024 but your  fact is only until November. Both are calculated with reference to your Dates table. I changed your calendar to this

_FY Calendar = 
ADDCOLUMNS (
    CALENDAR ( MIN ( Sheet1[Date] ), MAX ( Sheet1[Date] ) ),
    "Month", FORMAT ( [Date], "MMM" ),
    "Month Sort", MONTH ( EDATE ( [Date], -3 ) ),
    "Month Yr", FORMAT ( [Date], "YYYY-MM" ),
    "Qtr", FORMAT ( EDATE ( [Date], -3 ), "\QQ" ),
    "Year", YEAR ( [Date] ),
    "Fiscal Yr",
        VAR Check =
            MONTH ( [Date] ) >= 4
        VAR CY =
            RIGHT ( YEAR ( [Date] ), 2 )
        VAR NY =
            RIGHT ( YEAR ( [Date] ) + 1, 2 )
        VAR PY =
            RIGHT ( YEAR ( [Date] ) - 1, 2 )
        RETURN
            IF ( Check, "FY " & CY & "-" & NY, "FY " & PY & "-" & CY )
)

If you want to include dates beyond your fact table then you will need to month and quarter dimensions to your visual.

[AmeenVanakar]

@danextian could you also help me with a formula for the current fiscal year & previous fiscal year? 

Fiscal year = April to March

[AmeenVanakar]

@danextian  this worked! thanks for the help.