How to format Date Time values using FORMAT function in Power BI DAX

How to format Date and Time strings using FORMAT function in Power BI DAX

The FORMAT is a formatting function in DAX that formats a value based on a format string.

FormatDateTime=

VAR _DateVal  = DATE ( 2023, 3, 1 )

VAR _TimeVal  = TIME ( 15, 30, 5 )

VAR _DateTimeVal = (_DateVal + _TimeVal)

/* Formatting Date and Time Values */

VAR _DateValue1 = ROW ( "Value_to_Format", _DateTimeVal ,

                "Format_String","dd/mm/yyyy",

                "Formatted_Value",FORMAT (_DateTimeVal, "dd/mm/yyyy" ) )

VAR _DateTimeVal1 = ROW ( "Value_to_Format", _DateTimeVal ,

                "Format_String","mm/dd/yyyy hh:mm:ss AM/PM",

                "Formatted_Value",FORMAT (_DateTimeVal, "mm/dd/yyyy hh:mm:ss AM/PM" ) )

VAR _GeneralDate = ROW ( "Value_to_Format", _DateTimeVal ,

                "Format_String","General Date",

                "Formatted_Value",FORMAT (_DateTimeVal, "General Date" ) )

VAR _LongDate = ROW ( "Value_to_Format", _DateTimeVal ,"Format_String","Long Date",

                "Formatted_Value",FORMAT (_DateTimeVal, "Long Date" ) )

VAR _MedDate = ROW ( "Value_to_Format", _DateTimeVal ,"Format_String","Medium Date",

                "Formatted_Value",FORMAT (_DateTimeVal, "Medium Date" ) )

VAR _ShortDate = ROW ( "Value_to_Format", _DateTimeVal ,"Format_String","Short Date",

                "Formatted_Value",FORMAT (_DateTimeVal, "Short Date" ) )

VAR _US_DateFormat1 = ROW ( "Value_to_Format", _DateTimeVal ,"Format_String","en-US",

                "Formatted_Value",FORMAT (_DateTimeVal, BLANK(), "en-US" ) )

VAR _US_DateFormat2 = ROW ( "Value_to_Format", _DateTimeVal ,"Format_String","en-US",

                "Formatted_Value",FORMAT (_DateTimeVal,"mm/dd/yyyy", "en-US" ) )

VAR _UK_DateFormat1 = ROW ( "Value_to_Format", _DateTimeVal ,"Format_String","en-GB",

                "Formatted_Value",FORMAT (_DateTimeVal, BLANK(), "en-GB" ) )              

VAR _UK_DateFormat2 = ROW ( "Value_to_Format", _DateTimeVal ,"Format_String","en-GB",

                "Formatted_Value",FORMAT (_DateTimeVal, "dd/mm/yyyy", "en-GB" ) )

VAR _TimeValue1 = ROW ( "Value_to_Format", _DateTimeVal ,

                "Format_String","hh:mm:ss AM/PM",

                "Formatted_Value",FORMAT (_DateTimeVal, "hh:mm:ss AM/PM" ) )

VAR _TimeValue2 = ROW ( "Value_to_Format", _DateTimeVal ,"Format_String","hh:mm:ss",

                "Formatted_Value",FORMAT (_DateTimeVal, "hh:mm:ss" ) )

VAR _TimeValue3 = ROW ( "Value_to_Format", _DateTimeVal ,"Format_String","hh:nn:ss",

                "Formatted_Value",FORMAT (_DateTimeVal, "hh:nn:ss" ) )

VAR _LongTime = ROW ( "Value_to_Format", _DateTimeVal ,"Format_String","Long Time",

                "Formatted_Value",FORMAT (_DateTimeVal, "Long Time" ) )

VAR _MedTime = ROW ( "Value_to_Format", _DateTimeVal ,"Format_String","Medium Time",

                "Formatted_Value",FORMAT (_DateTimeVal, "Medium Time" ) )

VAR _ShortTime = ROW ( "Value_to_Format", _DateTimeVal ,"Format_String","Short Time",

                "Formatted_Value",FORMAT (_DateTimeVal, "Short Time" ) )

/* Format Day */

VAR _DayFormat1 = ROW ( "Value_to_Format", _DateTimeVal ,"Format_String","d",

                "Formatted_Value",FORMAT (_DateTimeVal, "d" ) )

VAR _DayFormat2 = ROW ( "Value_to_Format", _DateTimeVal ,"Format_String","dd",

                "Formatted_Value",FORMAT (_DateTimeVal, "dd" ) )

VAR _DayFormat3 = ROW ( "Value_to_Format", _DateTimeVal ,"Format_String","ddd",

                "Formatted_Value",FORMAT (_DateTimeVal, "ddd" ) )

VAR _DayFormat4 = ROW ( "Value_to_Format", _DateTimeVal ,"Format_String","dddd",

                "Formatted_Value",FORMAT (_DateTimeVal, "dddd" ) )

VAR _DayFormat5 = ROW ( "Value_to_Format", _DateTimeVal ,"Format_String","ddddd",

                "Formatted_Value",FORMAT (_DateTimeVal, "ddddd" ) )

VAR _DayFormat6 = ROW ( "Value_to_Format", _DateTimeVal ,"Format_String","dddddd",

                "Formatted_Value",FORMAT (_DateTimeVal, "dddddd" ) )

/* Format Month */

VAR _MonthFormat1 = ROW ( "Value_to_Format", _DateTimeVal ,"Format_String","m",

                "Formatted_Value",FORMAT (_DateTimeVal, "m" ) )

VAR _MonthFormat2 = ROW ( "Value_to_Format", _DateTimeVal ,"Format_String","mm",

                "Formatted_Value",FORMAT (_DateTimeVal, "mm" ) )

VAR _MonthFormat3 = ROW ( "Value_to_Format", _DateTimeVal ,"Format_String","mmm",

                "Formatted_Value",FORMAT (_DateTimeVal, "mmm" ) )

VAR _MonthFormat4 = ROW ( "Value_to_Format", _DateTimeVal ,"Format_String","mmmm",

                "Formatted_Value",FORMAT (_DateTimeVal, "mmmm" ) )

RETURN

UNION (

    _DateTimeVal1, _DateValue1,_GeneralDate,_LongDate,_MedDate,_ShortDate,

    _US_DateFormat1,_US_DateFormat2,_UK_DateFormat1,_UK_DateFormat2,

    _TimeValue1,_TimeValue2,_TimeValue3,_LongTime,_MedTime,_ShortTime,

    _DayFormat1,_DayFormat2,_DayFormat3,_DayFormat4,_DayFormat5,_DayFormat6,

    _MonthFormat1,_MonthFormat2,_MonthFormat3,_MonthFormat4

    ) Result:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------

How to format values using FORMAT function in Power BI DAX

How to format numbers using FORMAT function in Power BI DAX

The FORMAT is a formatting function in DAX that formats a value based on a format string.

Formatting Numbers:
FormatValues=

VAR _General1 = ROW ( "Value_to_Format", "abc.1234" ,"Format_String","General",

                "Formatted_Value",FORMAT ("abc.1234", "General" ) )

VAR _General2 = ROW ( "Value_to_Format", 1234,"Format_String","General",

                "Formatted_Value",FORMAT ("1234", "General" ) )

VAR _Standard = ROW ( "Value_to_Format", 1234.5678 ,"Format_String","Standard",

                "Formatted_Value",FORMAT (1234.5678, "Standard" ) )

VAR _Fixed = ROW ( "Value_to_Format", 1234.5678 ,"Format_String","Fixed",

                "Formatted_Value",FORMAT (1234.5678, "Fixed" ) )

VAR _Number1 = ROW ( "Value_to_Format", 1234.5678 ,"Format_String","#,0.00",

                "Formatted_Value",FORMAT (1234.5678, "#,0.00" ) )

VAR _Thousand = ROW ( "Value_to_Format", 1234567890 ,"Format_String","$#,0,.0#K",

                "Formatted_Value",FORMAT (1234567890, "$#,0,.0#K" ) )

VAR _Million = ROW ( "Value_to_Format", 1234567890 ,"Format_String","$#,0,,.0#M",

                "Formatted_Value",FORMAT (1234567890, "$#,0,,.0#M" ) )

VAR _Billion = ROW ( "Value_to_Format", 1234567890 ,"Format_String","$#,0,,,.0#B",

                "Formatted_Value",FORMAT (1234567890, "$#,0,,,.0#B" ) )

VAR _Trillion = ROW ( "Value_to_Format", 1234567890 ,"Format_String","$#,0,,,,.0#T",

                "Formatted_Value",FORMAT (1234567890, "$#,0,,,,.0#T" ) )  

VAR _Positive1 = ROW ( "Value_to_Format", 1234.12 ,"Format_String","#,0.00",

                "Formatted_Value",FORMAT (1234.12, "#,0.00" ) )

VAR _Positive2 = ROW ( "Value_to_Format", 1234.12 ,"Format_String","#,0.00;(#,0.00)",

                "Formatted_Value",FORMAT (1234.12, "#,0.00;(#,0.00)" ) )

VAR _Positive3 = ROW ( "Value_to_Format", 1234.12 ,"Format_String","#,#.##;(#,#.##);-",

                "Formatted_Value",FORMAT (1234.12, "#,#.##;(#,#.##);-" ) )

VAR _Negative1 = ROW ( "Value_to_Format", -1234.12 ,"Format_String","#,0.00",

                "Formatted_Value",FORMAT (-1234.12, "#,0.00" ) )

VAR _Negative2 = ROW ( "Value_to_Format", -1234.12 ,"Format_String","#,0.00;(#,0.00)",

                "Formatted_Value",FORMAT (-1234.12, "#,0.00;(#,0.00)" ) )

VAR _Negative3 = ROW ( "Value_to_Format", -1234.12 ,"Format_String","#,#.##;(#,#.##);-",

                "Formatted_Value",FORMAT (-1234.12, "#,#.##;(#,#.##);-" ) )

VAR _Percent1 = ROW ( "Value_to_Format", 0.2345,"Format_String","Percent",

                "Formatted_Value",FORMAT (0.2345, "Percent" ) )

VAR _Percent2 = ROW ( "Value_to_Format", 0.2345,"Format_String","#.00%",

                "Formatted_Value",FORMAT (0.2345, "#.00%" ) )

VAR _Percent3 = ROW ( "Value_to_Format", 0.2345,"Format_String","00.00%",

                "Formatted_Value",FORMAT (0.2345, "00.00%" ) )

VAR _Currency1 = ROW ( "Value_to_Format", 1234.5678,"Format_String","$#,0",

                "Formatted_Value",FORMAT (1234.5678, "$#,0" ) )

VAR _Currency2 = ROW ( "Value_to_Format", 1234.5678,"Format_String","$#.00",

                "Formatted_Value",FORMAT (1234.5678, "$#.00" ) )

VAR _Currency3 = ROW ( "Value_to_Format", 1234.5678,"Format_String","$#,0.00",

                "Formatted_Value",FORMAT (1234.5678, "$#,0.00" ) )

VAR _Currency4 = ROW ( "Value_to_Format", 1234.5678,"Format_String","""US$"" #,0.00",

                "Formatted_Value",FORMAT (1234.5678, """US$"" #,0.00" ) )

VAR _Currency5= ROW ( "Value_to_Format", 1234.5678,"Format_String","""€"" #,0.00",

                "Formatted_Value",FORMAT (1234.5678, """€"" #,0.00" ) )

VAR _Scientific = ROW ( "Value_to_Format", 1234.5678,"Format_String","Scientific",

                "Formatted_Value",FORMAT (1234.5678, "Scientific" ) )

VAR _Boolean1 = ROW ( "Value_to_Format", "True/False","Format_String","True/False",

                "Formatted_Value",FORMAT (TRUE(), "True/False" ) )

VAR _Boolean2 = ROW ( "Value_to_Format", "On/Off","Format_String","On/Off",

                "Formatted_Value",FORMAT (FALSE(), "On/Off" ) )

VAR _Boolean3 = ROW ( "Value_to_Format", "Yes/No","Format_String","Yes/No",

                "Formatted_Value",FORMAT (TRUE(), "Yes/No" ) )

/*

VAR _Zero1 = ROW ( "Value_to_Format", 0 ,"Format_String","#,0.00",

                "Formatted_Value",FORMAT (0, "#,0.00" ) )

VAR _Zero2 = ROW ( "Value_to_Format", 0 ,"Format_String","#,0.00;(#,0.00)",

                "Formatted_Value",FORMAT (0, "#,0.00;(#,0.00)" ) )

VAR _Zero3 = ROW ( "Value_to_Format", 0 ,"Format_String","#,#.##;(#,#.##);-",

                "Formatted_Value",FORMAT (0, "#,#.##;(#,#.##);-" ) )

VAR _Blank1 = ROW ( "Value_to_Format", BLANK() ,"Format_String","#,0.00",

                "Formatted_Value",FORMAT (BLANK(), "#,0.00" ) )

VAR _Blank2 = ROW ( "Value_to_Format", BLANK() ,"Format_String","#,0.00;(#,0.00)",

                "Formatted_Value",FORMAT (BLANK(), "#,0.00;(#,0.00)" ) )

VAR _Blank3 = ROW ( "Value_to_Format", BLANK() ,"Format_String","#,#.##;(#,#.##);-",

                "Formatted_Value",FORMAT (BLANK(), "#,#.##;(#,#.##);-" ) )

*/

RETURN

UNION (

    _General1,_General2, _Standard,_Fixed,_Number1,

    _Thousand, _Million,_Billion,_Trillion,

    _Positive1,_Positive2,_Positive3, _Negative1,_Negative2,_Negative3,

    _Percent1,_Percent2,_Percent3,

    _Currency1,_Currency2,_Currency3,_Currency4,_Currency5,

    _Scientific, _Boolean1,_Boolean2,_Boolean3

    /* _Zero1,Zero2,Zero3, _Blank1,Blank2,Blank3 */

    )

Result:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------

Microsoft Power BI Theme Colors with HEX Codes

Microsoft Power BI Themes Colors with Hex Codes Reference

--------------------------------------------------------------------------

List of popular UNICODE / UNICHAR Symbols (only for reference):

--------------------------------------------------------------------------

List of popular Colors with HEX Codes (only for reference):

--------------------------------------------------------------------------

List of popular Color Shades with HEX Codes (only for reference):

--------------------------------------------------------------------------

List of popular Gradient Colors with HEX Codes (only for reference):

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------