Fiscal year change with Interactive drill down

[anandprakashnm]

hello,

My Fiscal year is from Serptember to August and i am creating a dashboard where i want to change fiscal year and rertain drill down capabilities by quarter and month.

Help!!

[v-haibl-msft · Accepted solution]

@anandprakashnm

You can create columns of your FiscalYear, FiscalQuarter and FiscalMonth with following formulas.

FiscalYear = 
IF (
    MONTH ( Table1[Date] ) <= 8,
    YEAR ( Table1[Date] ) - 1,
    YEAR ( Table1[Date] )
)

FiscalMonth = 
IF (
    MONTH ( Table1[Date] ) < 9,
    MONTH ( Table1[Date] ) + 4,
    MONTH ( Table1[Date] ) - 8
)

FiscalQuarter = 
ROUNDUP ( [FiscalMonth] / 3, 0 )

FiscalMonthName = 
VAR TempFiscalDate =
    DATEVALUE ( Table1[FiscalMonth] & "/1/" & Table1[FiscalYear] )
RETURN
( FORMAT ( TempFiscalDate, "mmm" ) )

Then create a new table with following formula and select FiscalMonthName to make it sorted by FiscalMonth column.

Table = 
SUMMARIZECOLUMNS ( Table1[FiscalMonthName], Table1[FiscalMonth] )

In the canvas, select the new created columns as below and enable drill down.

Best Regards,

Herbert

[anandprakashnm]

How to do this? What is FiscalMonthName =???? and also VAR TEMPFISCALDATE??

FiscalMonthName =
VAR TempFiscalDate =
    DATEVALUE ( Table1[FiscalMonth] & "/1/" & Table1[FiscalYear] )
RETURN
( FORMAT ( TempFiscalDate, "mmm" ) )

[v-haibl-msft]

@anandprakashnm

I created several calculated columns here. FiscalMonthName is a new created column. For VAR, we can define many variables and they are local to the expression in which you define them.

I’ve also uploaded my Power BI file here for reference.

Best Regards,

Herbert

[anandprakashnm]

Thanks. It helped.