Create a custom table for fiscal year from July'23 to June'24

[sarav_093]

Created data table using above DAX code, I wanted to define fiscal year from July'23 to June'24 by dynamically once i have data for FY25 and FY26. Also create quarter based on July'23 to June'24 using DAX.

Fiscal Calendar =
VAR MinYear = 2023
VAR MaxYear = 2024
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO( ),
AND ( YEAR([Date]) >= MinYear, YEAR([Date]) <= MaxYear )
),
"Month", FORMAT([Date], "MMM"),
"Month Num", Month([Date]))

[Anonymous · Accepted solution]

Hi @sarav_093 ,

Thanks for reaching out to us about your problem. Base on your description, it seems that you want to create a calculated table dynamically base on the slicer selection. I'm afraid that it is not possible to get it. You can follow the steps to get it as an workaround:

1. Create a fiscal calendar table with the full dates(include the field [Fiscal Year])

2. Create a dimension table as below(DO NOT create any relationship with the previous calendar table) and apply the field on the slicer

Fiscal Year

FY21

FY22

FY23

FY24

FY25

FY26

3. Create a measure as below(Assume that from July'23 to June'24 is on FY24 )

Flag =
VAR _fy =
    MIN ( 'FYdimension'[Fiscal Year] )
VAR _calendarfy =
    'FY' & "FY"
        & ( VALUE ( RIGHT ( _fy, 2 ) ) - 1 )
VAR _dates =
    CALCULATETABLE (
        VALUES ( 'Calendar'[Date] ),
        FILTER ( 'Calendar', 'Calendar'[Fiscal Year] = _calendarfy )
    )
RETURN
    IF ( SELECTEDVALUE ( 'Calendar'[Date] ) IN _dates, 1, 0 )

4. Create a table visual and apply the field [Date] of calendar table on it. Then apply a visual-level filter with the condition(Flag is 1)

Best Regards