Display dates

[Anonymous]

I have a column year-mon

That contains data like:

2021-02

2021-03

2021-01

How can display a calculated column like:

2021-02-28

2021-03-31

2021-01-31

For the current month, I need to display today. For example for tomorrow I will have a data like

2021-02-28

2021-03-31

2021-01-31

2021-04-01

I mean for the current month I need to display the current date and the last months I need to display the end of month. 

[selimovd]

Hey @Anonymous ,

can you do the changes in Power Query or do you have to do that in Power BI?

If you can do it in Power Query, check the following code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1MFKK1YGxjZHYhkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"year-mon" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"year-mon", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "DateEndOfMonth", each Date.EndOfMonth([#"year-mon"]))
in
    #"Added Custom"

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[Anonymous]

Hi @selimovd I need to do it within Dax in power bi. 

[selimovd · Accepted solution]

Hey @Anonymous ,

in DAX it's also possible.

Add a calculated column with the following code:

EndOfMonth =
VAR vYEAR = LEFT( MyTable[year-mon], 4 )
VAR vMonth = RIGHT( MyTable[year-mon], 2 )
VAR vDateFirst = DATE( vYEAR, vMonth, 1 )
RETURN
    EOMONTH( vDateFirst, 0 )

Let me know if that worked.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi