achieving previous FY + 3 months of current FY in DAX

[AG_x11x]

Hi friends,

I need a DAX formula for my Custom Financial year.

I would like to add a column  showing:

my financial year is 1st July 2022 -  30th June 2023 and dax formula should display: 1) if today's date is October or greater then show TRUE for dates falling within current financial year. 2) However, if today's date has recently entered a new financial period and current date falls within July, August, september (ie first 3 months of FY Period) then TRUE should display for dates falling within previous FY and up to dates within July, Aug, Sep of current financial year period. otherwise, false if neither of these conditions are met. 

Expected results if current month is August:

FY	Year	Month	Month Number	Desired Result
2023/2024	2023	august	8	TRUE
2023/2024	2023	july	7	TRUE
2022/2023	2022	june	6	TRUE
2022/2023	2022	may	5	TRUE
2022/2023	2022	april	4	TRUE
2022/2023	2022	march	3	TRUE
2022/2023	2022	february	2	TRUE
2022/2023	2022	january	1	TRUE
2022/2023	2022	december	12	TRUE
2022/2023	2022	november	11	TRUE
2022/2023	2022	october	10	TRUE
2022/2023	2022	september	9	TRUE
2022/2023	2022	august	8	TRUE
2022/2023	2022	july	7	TRUE
2021/2022	2022	june	6	FALSE
2021/2022	2022	may	5	FALSE
2021/2022	2022	april	4	FALSE
2021/2022	2022	march	3	FALSE
2021/2022	2022	february	2	FALSE
2021/2022	2022	january	1	FALSE

Expected results if current month is November:

FY	Year	Month	Month Number	Desired Result
2023/2024	2023	november	11	TRUE
2023/2024	2023	october	10	TRUE
2023/2024	2023	september	9	TRUE
2023/2024	2023	august	8	TRUE
2023/2024	2023	july	7	TRUE
2022/2023	2022	june	6	FALSE
2022/2023	2022	may	5	FALSE
2022/2023	2022	april	4	FALSE
2022/2023	2022	march	3	FALSE
2022/2023	2022	february	2	FALSE
2022/2023	2022	january	1	FALSE
2022/2023	2022	december	12	FALSE
2022/2023	2022	november	11	FALSE
2022/2023	2022	october	10	FALSE
2022/2023	2022	september	9	FALSE
2022/2023	2022	august	8	FALSE
2022/2023	2022	july	7	FALSE
2021/2022	2022	june	6	FALSE
2021/2022	2022	may	5	FALSE
2021/2022	2022	april	4	FALSE
2021/2022	2022	march	3	FALSE
2021/2022	2022	february	2	FALSE
2021/2022	2022	january	1	FALSE

Current script: 

Central_Calendar = ADDCOLUMNS(

CALENDAR(DATE(2022,1, 1), DATE(2030,1,1))

, "Year", YEAR([DATE])

, "MonthNumber", MONTH([DATE])

, "Month", FORMAT([DATE], "MMM")

, "Quarter", "Q " & FORMAT([DATE], "Q")

, "QuarterNumber", FORMAT([DATE], "Q")

, "Year Quarter", YEAR([Date]) & " " & "Q" & QUARTER([Date])

, "Year Quarter Sort", YEAR([Date]) & QUARTER([Date])

, "YearMonthNumber", FORMAT([DATE], "yyyy-mm")

, "Month Year", FORMAT([DATE], "MMM YY")

, "Month Year Sort", YEAR([Date]) *100 + MONTH([Date])

, "Fiscal Year", YEAR(EDATE([DATE], 6))

, "FY", "FY" & IF(MONTH([Date])<7,

YEAR([Date])-1 & "/" & FORMAT([Date], "YY"), YEAR([Date]) & "/" & FORMAT(EOMONTH([DATE], 6), "YY"))

, "Fiscal Month", MONTH(EDATE([DATE], 6))

, "Fiscal Quarter", QUARTER(EDATE([DATE], 6))

, "Fiscal Week", WEEKNUM(EDATE([DATE], 6))

, "Last 6 Months", IF(EOMONTH([DATE], 0) > EOMONTH(TODAY(), -6) && [date] <= TODAY(), TRUE())

, "Current_FY_to_Date", IF([DATE] >= DATE(YEAR(TODAY()) -1, 7, 1) && [DATE] <= TODAY(),TRUE(), FALSE())

, "Previous_FY", IF([DATE] >= DATE(YEAR(TODAY()) -2, 7,1) && [DATE] <= DATE(YEAR(TODAY()) -1, 6, 30),TRUE(), FALSE())

, "Previous_FY_Month", IF([Date] >= EDATE(TODAY(), -1) && [Date] <= EOMONTH(TODAY(), -1) && [DATE] >= DATE(YEAR(TODAY()) -1, 7,1), TRUE(), FALSE())

, "Previous_Month", IF([Date] >= EDATE(TODAY(), -1) && [Date] <= EOMONTH(TODAY(), -1), TRUE(), FALSE())
, "Current_FY_or Prev_FY_plus 2 months of current FY",
IF (
    AND(
        TODAY() >= DATE(YEAR(TODAY()), 7, 1),   // Check if today's date is between July 1 of the current fiscal year
        TODAY() <= DATE(YEAR(TODAY()), 9, 30)    // and September 30 of the current fiscal year
    ),
    OR(
       YEAR(EDATE([DATE], 6))  = YEAR(TODAY()) - 1,   // Return TRUE for dates of the previous fiscal year
        [DATE] = DATE(YEAR(TODAY()), 9, 30)   // Return TRUE for September 30 of the current fiscal year
    ),
    IF (
        AND(
            TODAY() >= DATE(YEAR(TODAY()), 10, 1),   // Check if today's date is between October 1 of the current fiscal year
            TODAY() <= DATE(YEAR(TODAY()) + 1, 6, 30)   // and June 30 of the next fiscal year
        ),
        IF (
            [DATE] >= DATE(YEAR(TODAY()) , 7, 1) && [DATE] <= TODAY()   // Return TRUE for dates of the previous fiscal year to date
            TRUE(),
            FALSE()
        ),
        FALSE   // Return FALSE if neither condition is met
    )
))

any suggestions on how to achieve this would be appreciated.

[Anonymous]

Hi @AG_x11x ,

Please have a try.

Custom Financial Year =
VAR CurrentMonth =
    MONTH ( TODAY () )
VAR CurrentYear =
    YEAR ( TODAY () )
VAR PreviousYear = CurrentYear - 1
VAR StartDate =
    DATE ( PreviousYear, 7, 1 )
VAR EndDate =
    DATE ( CurrentYear, 6, 30 )
VAR PreviousFYEndDate =
    DATE ( PreviousYear, 8, 31 )
VAR Result =
    IF (
        CurrentMonth >= 10,
        IF ( AND ( [Date] >= StartDate, [Date] <= EndDate ), TRUE (), FALSE () ),
        IF (
            AND ( [Date] >= DATE ( PreviousYear, 7, 1 ), [Date] <= PreviousFYEndDate ),
            TRUE (),
            FALSE ()
        )
    )
RETURN
    Result

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AG_x11x]

Hi @Anonymous,
I really appreciate your help, but the results only displayed data for the previous financial year despite being in November. The correct information should cover the current financial year from July 1, 2023, to today. (i.e if greater than or equal to October of the current FY, it should show 'TRUE' for dates for the current financial year to day i.e between 1 July 2023 till today. If less than October of the current FY, it should show previous FY up until September of current FY)

Attached is the pbix file with the mentioned results. https://drive.google.com/file/d/1XEY7SJBJ5nBw_Hqs7KBI6gAcGskZxoqa/view?usp=sharing 

my current code so far but not working if it the date was e.g August:
 "Current_FY_or Prev_FY_plus 2 months of current FY",
IF (
    AND(
        TODAY() >= DATE(YEAR(TODAY()), 7, 1),   // Check if today's date is between July 1 of the current fiscal year
        TODAY() <= DATE(YEAR(TODAY()), 9, 30)    // and September 30 of the current fiscal year
    ),
    OR(
       YEAR(EDATE([DATE], 6))  = YEAR(TODAY()) - 1,   // Return TRUE for dates of the previous fiscal year
        [DATE] = DATE(YEAR(TODAY()), 9, 30)   // Return TRUE for September 30 of the current fiscal year
    ),
    IF (
        AND(
            TODAY() >= DATE(YEAR(TODAY()), 10, 1),   // Check if today's date is between October 1 of the current fiscal year
            TODAY() <= DATE(YEAR(TODAY()) + 1, 6, 30)   // and June 30 of the next fiscal year
        ),
        IF (
            [DATE] >= DATE(YEAR(TODAY()) , 7, 1) && [DATE] <= TODAY(),   // Return TRUE for dates of the previous fiscal year to date
            TRUE(),
            FALSE()
        ),
        FALSE   // Return FALSE if neither condition is met
    )
))