Forum Discussion

ERing's avatar
ERing
Post Partisan
3 years ago

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

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?




Need Help

1 Reply

  • jennratten's avatar
    jennratten
    Super User
    3 years ago

    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"