Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!!
Solved! Go to Solution.
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
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
How to do this? What is FiscalMonthName =???? and also VAR TEMPFISCALDATE??
FiscalMonthName =
VAR TempFiscalDate =
DATEVALUE ( Table1[FiscalMonth] & "/1/" & Table1[FiscalYear] )
RETURN
( FORMAT ( TempFiscalDate, "mmm" ) )
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
Thanks. It helped.
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |