Hi Community,
I am attempting to use the DATE table DAX formula in the DAX Patterns book but I keep getting this following error:
Argument '3' in FORMAT function is required
I copied the code to the best of my ability from what I can see but I can seem to find where I'm going wrong. Here is the following code that I currently have.
Date =
VAR FirstFiscalMonth = 1
VAR MonthsInYear = 12
VAR CalendarFirstDate =
MIN(
sap_data[Date]
)
VAR CalendarLastDate =
MAX(
sap_data[Date]
)
VAR CalendarFirstYear =
YEAR(
CalendarFirstDate
)
VAR CalendarFirstMonth =
MONTH(
CalendarFirstDate
)
VAR CalendarLastYear =
YEAR(
CalendarLastDate
)
VAR CalendarLastMonth =
MONTH(
CalendarLastDate
)
VAR GranularityByDate =
ADDCOLUMNS(
CALENDAR(
DATE(
CalendarFirstYear, CalendarFirstMonth, 1
),
EOMONTH(
DATE(
CalendarLastYear, CalendarLastMonth, 1
),
0
)
),
"Year Month Number", YEAR(
[Date]
) * MonthsInYear + MONTH(
[Date]
)-1
)
VAR GranularityByMonth =
SELECTCOLUMNS(
GENERATESERIES(
CalendarFirstYear * MonthsInYear + CalendarFirstMonth -1
- (MonthsInYear - 12) * (CalendarFirstMonth < FirstFiscalMonth),
CalendarLastYear * MonthsInYear + CalendarLastMonth -1
- (MonthsInYear -12) * (CalendarLastMonth < FirstFiscalMonth),
1
),
"Year Month Number", [Value]
)
RETURN GENERATE(
GranularityByMonth,
VAR YearMonthNumber =[Year Month Number]
VAR FiscalMonthNumber =
MOD(
YearMonthNumber +1
* (FirstFiscalMonth > 1)
* (MonthsInYear + 1 - FirstFiscalMonth),
MonthsInYear
) + 1
VAR FiscalYearNumber =
QUOTIENT(
YearMonthNumber + 1
* (FirstFiscalMonth > 1)
* (MonthsInYear + 1 - FirstFiscalMonth),
MonthsInYear
)
VAR OffsetFiscalMonthNumber =
MonthsInYear + 1
- (MonthsInYear -12)
VAR MonthNumber =
IF(
FiscalMonthNumber <= 12 && FiscalMonthNumber > 1,
FiscalMonthNumber + FirstFiscalMonth
- IF(
FiscalMonthNumber > (OffsetFiscalMonthNumber - FirstFiscalMonth),
OffsetFiscalMonthNumber,
1
),
FirstFiscalMonth
)
VAR YearNumber =
FiscalYearNumber - 1
* (MonthNumber > FiscalMonthNumber)
VAR YearMonthKey =
YearNumber * 100 + MonthNumber
VAR MonthDate =
DATE(
YearNumber, MonthNumber, 1
)
VAR FiscalQuarterNumber =
MIN(
ROUNDUP(
FiscalMonthNumber / 3, 0
), 4
)
VAR FiscalYearQuarterNumber =
FiscalYearNumber * 4 + FiscalQuarterNumber -1
VAR FiscalMonthInQuarterNumber =
MOD(
FiscalMonthNumber -1, 3
) + 1 + 3 * (MonthNumber > 12)
VAR MonthInQuarterNumber =
MOD(
MonthNumber -1, 3
) + 1 +3 * (MonthNumber > 12)
VAR QuarterNumber =
MIN(
ROUNDUP(
MonthNumber / 3, 0
), 4
)
VAR YearQuarterNumber =
YearNumber * 4 + QuarterNumber -1
RETURN
ROW(
"Year Month Key", YearMonthKey,
"Year", YearNumber,
"Year Quarter", FORMAT(
QuarterNumber, "\Q0"
) & "-" & FORMAT(
YearNumber, "000"
),
"Year Quarter Number", YearQuarterNumber,
"Quarter", FORMAT(
QuarterNumber, "\Q0"
),
"Year Month", IF(
MonthNumber > 12,
FORMAT(
MonthNumber, "\M00"
) &
FORMAT(
YearNumber, "0000"
),
FORMAT(
MonthDate, "mmm yyyy"
)
),
"Month", IF(
MonthNumber > 12,
FORMAT(
MonthNumber, "\M00",
),
FORMAT(
MonthDate, "mmm"
)
),
"Month Number", MonthNumber,
"Month In Quarter Number", MonthInQuarterNumber,
"Fiscal Year", FORMAT(
FiscalMonthNumber, "\F\Y 0000"
),
"Fiscal Year Number", FiscalYearNumber,
"Fiscal Year Quarter", FORMAT(
FiscalQuarterNumber, "\F\Q0"
) & "-" & FORMAT(
FiscalYearNumber, "0000"
),
"Fiscal Year Quarter Number", FiscalMonthInQuarterNumber,
"Fiscal Quarter", FORMAT(
FiscalQuarterNumber, "\F\Q0"
),
"Fiscal Month", IF(
MonthNumber > 12,
FORMAT(
MonthNumber, "\M00"
),
FORMAT(
MonthDate, "mmm"
)
),
"Fiscal Month Number", FiscalMonthNumber,
"Fiscal Month In Quarter Number", FiscalMonthInQuarterNumber
)
)
