hello Team power bi
thanks in avance for your help
I have a especific fiscal calendar start in april finish in march, and funcionality is ok
FiscalCalendar II =
VAR BaseCalendar = CALENDARAUTO(3)
RETURN
GENERATE(
BaseCalendar;
VAR BaseDate = [Date]
VAR YearDate = YEAR ( BaseDate )
VAR MonthNumber = MONTH ( BaseDate )
VAR FiscalMontNumber = IF(MonthNumber >= 4; MonthNumber - 3; MonthNumber + 9)
VAR FiscalQuarter = IF(FiscalMontNumber < 4;
"Q1";
IF(FiscalMontNumber < 7;
"Q2";
IF(FiscalMontNumber < 10;
"Q3";
"Q4"
)
)
)
VAR FiscalYear = IF(MonthNumber < FiscalMontNumber; YearDate - 1; YearDate+1)
RETURN ROW (
"Month"; FORMAT( BaseDate; "mmmm");
"FiscalMonthNumber"; FiscalMontNumber;
"FiscalMonth"; FORMAT( BaseDate; "mmm");
"FiscalQuarter"; FiscalQuarter;
"FiscalYearQuarter"; FiscalYear & " " & FiscalQuarter;
"FiscalYear"; FiscalYear;
"FiscalYearMonth"; FiscalYear & " " & FiscalMontNumber;
"FY";CONCATENATE("FY";RIGHT(FiscalYear;2))
))
hi rdiazd73
You'd better transform the dummy data when you load new table in edit queries and set the column as a date column.
https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data
Regards,
Lin
rdiazd73 , I have generated a fiscal calendar from April to Sep. Please check that. If you play around with Year Start date and year-end date and month calculation you can get the same.
https://www.dropbox.com/s/wrcyk5j66corvjg/Apr2Mar-Cal.pbix?dl=0
thank you so much for your answer
for your idea of add +++colums in table calendar
I´m try solution including in power querry a +new column in origin table of data
fiscar year/period fiscal year/period 2
03 2020 3 2020
hi rdiazd73
You'd better transform the dummy data when you load new table in edit queries and set the column as a date column.
https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data
Regards,
Lin
