Can I switch the Date Format of a field based on a logic, if date is within a month, then as Quarter

[Anonymous]

How can I switch Date Format for a field in Paginated report based on a logic.

The logic would be as follows:

• (Finish Date – Current Date) > 3 Months – Display Finish Date as a Quarter (i.e. Q1.2022)
• (Finish Date – Current Date) <= 3 Months and (Finish Date – Current Date) > 1 Month – Display Finish Date as Month (i.e. February, 2022)
• (Finish Date – Current Date) <= 1 Month – Display Finish Date as Date (i.e. 2/15/2022)

My current expression for that field is as below-

=FormatDateTime(Fields!Finish_Date.Value, Microsoft.VisualBasic.DateFormat.ShortDate)

Thank you in advance.

[HotChilli]

The comma at the end requires a default value afterwards OR delete the comma if a default case is not required

[Anonymous]

Thank you for catching that. I made the change and now I am getting the "Expressions that yield variant data-type cannot be used to define calculated columns" error. 

I am not sure if you can use calculated column to solve my original problem. Maybe, I need to do something within the Paginated Report (Finish Date Query) to achieve this.

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

Only one data type can exist in the same column, and the calculated column [Column] has data of both Date type ([Finish Date]) and Text type ( FORMAT ( [Finish Date], "MMMM, YYYY" ) and FORMAT ( [Finish Date], "QQ-YYYY") ), so it reported variant data-type error... You can change its formula to the following one:

DAX Fridays! #148: CONVERT and resolve variant data-type error

Column =

VAR _1 =

DATEDIFF (

TODAY (),

'Consolidated In Progress Status Report'[Finish Date],

MONTH

)

RETURN

SWITCH (

TRUE (),

_1 < 1, FORMAT ( [Finish Date], "MM/DD/YYYY" ),

_1 <= 3, FORMAT ( [Finish Date], "MMMM, YYYY" ),

_1 > 3, FORMAT ( [Finish Date], "QQ-YYYY" )

)

Best Regards

[HotChilli]

Yes, there is an error in the code given to you.  I have faith in you to make the fix.  Start at the beginning of the squiggly red line.

[Anonymous]

I think I am very close. I just have one final error on the Switch syntax (that I need to figure out)

Column =

var _1 = datediff(TODAY(), 'Consolidated In Progress Status Report'[Finish Date] , Month)

return

Switch( True() ,

_1< 1, [Finish Date],

_1< 3, format([Finish Date], "MMMM, YYYY"),

format([Finish Date], "QQ-YYYY"),

)

[HotChilli]

You've copied the code verbatim but you need to create the column (with a name) and then put the code in.  

-1 is a variable which should be declared within the column.  That's why you're getting syntax errors.

[Anonymous]

@HotChilli Thank you for taking a look. I made some change (per your comment). I am getting "Too many arguments were passed" error now.

My update code is"

Column =

var _1 = datediff(TODAY(), 'Consolidated In Progress Status Report'[Finish Date] , Month)

return

Switch( True() ,

_1< 1, [Finish Date],

_1< 3, format([Finish Date], "MMMM, YYYY",

format([Finish Date], "QQ-YYYY"

)))

[amitchandak]

@Anonymous , a new column

var _1 = datediff(Current Date, Finish Date , Month)

return

Switch( True() ,

_1< 1, [Finish Date],

_1 <3, format([Finish Date], "MMMM, YYYY",

format([Finish Date], "QQ-YYYY"

)

[Anonymous]

@amitchandak I am still getting the "Arugment '8' in SWITCH fucntion is required" error, is there in the DAX code below-

Column =
var _1 = datediff(TODAY(), 'Consolidated In Progress Status Report'[Finish Date] , Month)
return
Switch(True() ,
_1< 1, [Finish Date],
_1<= 3, format([Finish Date], "MMMM, YYYY"),
_1> 3, format([Finish Date], "QQ-YYYY"),
)

Thank you for your time.

[Anonymous]

@amitchandak  Thank you for your quick response. So, I created a new calculated column on the same table (where the Finish date exists). I replaced the Current Date to TODAY (). But I am getting the following error (not sure what I am doing wrong)-

My updated query is-

var _1 = datediff(TODAY(), 'Consolidated In Progress Status Report'[Finish Date], month)

return

Switch( True() ,

_1< 1, [Finish Date],

_1 <3, format([Finish Date], "MMMM, YYYY",

format([Finish Date], "QQ-YYYY"

)