Forum Discussion

paulojul's avatar
paulojul
Regular Visitor
3 years ago
Solved

DAX Patterns Date Table

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
)
)

Need Help
  • PabloDeheza's avatar
    PabloDeheza
    3 years ago

    Hi there!

    You have one more comma than you should on your eighth FORMAT from bottom to top.

     

    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
            )
    )

     

    That should fix it.

    Please consider accepting this answer as a solution if it solved your problem!

1 Reply

  • PabloDeheza's avatar
    PabloDeheza
    Solution Sage
    3 years ago

    Hi there!

    You have one more comma than you should on your eighth FORMAT from bottom to top.

     

    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
            )
    )

     

    That should fix it.

    Please consider accepting this answer as a solution if it solved your problem!