Combine Date formats in the same Column

[RicLup]

Hi all

Hoping you can help me, if is possible combine date format in a column? I need to have a column like this in the expected date field:

As you can see i need to convert the actual date field with values = 01/01/YYYY for each year, because i have a total sales amount for month and year. I need to show the totals in a table with the format mentioned in the table above.

Regards and thanks a lot!!

[v-alq-msft · Accepted solution]

Hi, @RicLup 

In Power BI Desktop, a column can only have a data type. If you want to have a column with two format("yyyy", "mm/dd/yyyy"). I'd like to suggest you try the following calculated column to get the result with text data type. I created data to reproduce your scenario.

Table:

Calculated column:

Formatted Date = 
IF(
    MONTH([Actual Date])=1&&DAY([Actual Date])=1,
    FORMAT([Actual Date],"yyyy"),
    FORMAT([Actual Date],"mm/dd/yyyy")
)

Result:

Best Regards

Allan

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

[RicLup]

thanks a lot @v-alq-msft works great too!!

[Ashish_Mathur]

Hi,

Why in the first place do you want to do this?

[mussaenda]

Hi @RicLup,

I have assumed that it is okay on your side to convert your dates into string.

I did this in M Query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc2xDcBACEPRXahPwpg0mQWx/xoJxSW+8uthUWUZjnCCsKXRq4y3g9skxuY0dZdicFyf/TGG/SJekziMajwt1dK6Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Actual Date" = _t, #"Expected Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Actual Date", type date}, {"Expected Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains(Date.ToText([Expected Date]), "2020")
then [Expected Date]
else Text.AfterDelimiter(Date.ToText([Expected Date]), "/",{0, RelativePosition.FromEnd}))
in
    #"Added Custom"

Hope this helps!

[Greg_Deckler]

@RicLup - If that column is text. I'm not sure what you are trying to do though exactly

[RicLup]

Hello @Greg_Deckler

Thanks for the tracking, the actual date of the column is the date type, so I can't combine the values that change the format of the types,

I can only use one option for all values, for example:

if I apply the year date option all values change to year:

This is the result;

So I need the result like this in date format:

Thanks a lot!!

[Greg_Deckler]

@RicLup - Right, I guess what I was saying or trying to say was to in your Power Query don't convert it to a date and instead convert it to text (I'm not sure how it is represented in your source data) or create a new column like:

Expected Date = IF(DAY([Actual Date])=1&&MONTH([Actual Date])=1,YEAR([Actual Date])&"",[Actual Date]&"")

[RicLup]

Tahnks a lot @Greg_Deckler  , works great!