date < current month as arrears

[Vinod_P]

I am creating a reference data column , Trying to get all the dates less than current month as arrears 

[v-yangliu-msft · Accepted solution]

Hi  @Vinod_P ,

This is by design in Power BI, you can't have two data types in a column, you can apply the Format() formula to make the date appear as text:

Column =
IF(
    MONTH('Table'[Date]) < MONTH(TODAY()),"arrears",FORMAT('Table'[Date],"yyyy-mm-dd"))

There are many more date text formats that you can view in the following links:

FORMAT function (DAX) - DAX | Microsoft Learn

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yangliu-msft]

Hi  @Vinod_P ,

Here are the steps you can follow：

1. Create calculated column.

Column =
IF(
    MONTH('Table'[Date]) < MONTH(TODAY()),"arrears","flag")

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Vinod_P]

Hi Liu, Thanks for the quick solution. 

Is it possible to bring date instead of flag? 

I tried this but I am getting "Expressions that yield variant data-type cannot be used to define calculated columns." --> Probably not accepting text & date in one column. Any light you can shed on this please. 

[Ritaf1983]

Hi @Vinod_P 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523