Forum Discussion
Display dates
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.
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 regardsDenisBlog: WhatTheFact.bi
3 Replies
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 regardsDenisBlog: WhatTheFact.biHi selimovd I need to do it within Dax in power bi.
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 regardsDenisBlog: WhatTheFact.bi