Forum Discussion

anandprakashnm's avatar
anandprakashnm
Frequent Visitor
10 years ago
Solved

Fiscal year change with Interactive drill down

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's avatar
    v-haibl-msft
    10 years ago

    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

4 Replies

  • v-haibl-msft's avatar
    v-haibl-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    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's avatar
      anandprakashnm
      Frequent Visitor
      10 years ago

      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's avatar
        v-haibl-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        10 years ago

        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