Add column that is = (Spend/Days in month)

[ERing]

I'm struggling to accomplish my need with DAX so I thought I'd give a Power Query solution a try.

My table looks like below. My goal is to add a column where the result for each row is = (monthly spend / days in month).

So for each row in April, I'd like to show $16.67 ($500/ 30 days in April) and for each row in May I'd like to show $12.90 ($400/31 days in May).

Anyone know how this can be done?

[jennratten]

Hello - this is how you can do it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdK7DcAgEATRXoiRYBfwpxbk/tuwnHonneiebvcus6m5e5RaVu/lqV9yppFpZlqZjkxnpivTnUkdGtwvAAgEAoLAIEAIFAKGwGFwmP4ADoPD4DA4DA6Dw+AwOEY4Vs5p/eb0vA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Monthly Spend" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Monthly Spend", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "DaysInMonth", each Date.DaysInMonth([Date]), Int64.Type),
    #"Inserted Division" = Table.AddColumn(#"Added Custom", "Daily Spend", each [Monthly Spend] / [DaysInMonth], Currency.Type)
in
    #"Inserted Division"