Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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"
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |