Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Date value
Jan012022 50
Feb012022 60
Mar012022 40
The data available montly wise we need monthy data convert daily with respective month
No of days respective month
Out put
Date value
Jan012022 50/31
Jan022022 50/31
Jan032022 50/31
That month value generate continuous dates for respective month and divide no of days for that monthly value
HI @Patan7766,
You can use following calculated column expression to extract the days from the current date string to calculate with the value column.
Days =
VAR _date =
DATEVALUE (
LEFT ( [Date], 3 ) & "/"
& LEFT ( RIGHT ( [Date], 6 ), 2 ) & "/"
& RIGHT ( [Date], 4 )
)
RETURN
DAY ( DATE ( YEAR ( _date ), MONTH ( _date ) + 1, 1 ) - 1 )
Regards
Xiaoxin Sheng
OK. What have you tried and where are you stuck? Do you plan to do this in Power Query or in DAX?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMMzA0MjAyUtJRMjVQitWJVnJLTYILmUGEfBOL4EImQKFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
#"Replaced Value" = Table.ReplaceValue(Source,"01"," ",Replacer.ReplaceText,{"Date"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Date", type date}, {"Value", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "End", each Date.EndOfMonth([Date]),type date),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Day", each {Int32.From([Date])..Int32.From([End])}),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Days", each List.Count([Day]),Int32.Type),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom2", "Day"),
#"Added Custom3" = Table.AddColumn(#"Expanded Custom", "Day Value", each [Value]/[Days],type number),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom3",{"Day", "Day Value"})
in
#"Removed Other Columns"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
User | Count |
---|---|
15 | |
5 | |
2 | |
1 | |
1 |
User | Count |
---|---|
22 | |
3 | |
3 | |
2 | |
2 |