Monthly value by week

[Oomsen]

I would like to show a monthly value by week.

Month      Week     Value

January     1            337.500

                  2

                  3

                  4 

                  5

February    6           335.000

                  7

Currently the value is only shown by month. I would like to see 337.500 in week 2, 3, 4 and 5.

The data is add from a custom excelsheet.  

[Mariusz]

Hi @Oomsen 

Please see the below creenshot

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Oomsen]

@Mariusz 

This isn't the solution unfortunately. 

[Mariusz]

Hi @Oomsen 

Is there a reason why?

Best Regards,
Mariusz

Please feel free to connect with me.

[Oomsen]

@Mariusz  i don't know. But the result in my report doesn't change. 

Maybe another way is to say that from 1-1-19 to 1-2-19 the value is xxx and from 1-2-19 to 1-3-19 it's xxy?

[Mariusz]

Hi @Oomsen 

Is this the result you aiming for? then the instructions I gave you earlier should result in this.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Oomsen]

@Mariusz  

My current data is like this:

Date              Value        End date

1-1-2018       367500      31-1-2018

1-2-2018       365000      28-2-2018

1-3-2018       362500      31-3-2018

So where do i need to filldown?

[Mariusz · Accepted solution]

Hi @Oomsen 

Please see the below M code, this will expand your months to weeks.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjA0MLJR2lQwsUYMjYzNzUwODQAqCoMUJJrA5EhxFWHUANEB1GFnAlMB3GWHUYIdsBVRIbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t, #"End date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}, {"End date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Distinct( List.Transform( List.Dates( [Date],  Duration.Days( [End date] - [Date] ), #duration( 1, 0, 0, 0 ) ), each Date.WeekOfYear(_) ) )),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type number}})
in
    #"Changed Type1"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[v-chuncz-msft]

@Oomsen 

You may use Table.FillDown in Query Editor.

= Table.FillDown(#"Changed Type",{"Value"})

[Oomsen]

@v-chuncz-msft 

Thanks for your reaction. 

Can you be a little more specific where i need to add this in the query editor.