Amount breakdown in 13 week bucket

[Sunila]

Hi, 

I've encountered a challenge while working on weekly bucket. I intend to display a distribution of weekly amounts for the upcoming 13 weeks. I attempted to link Table A's "WeekStartDate" with Table B's "date" column to retrieve the amount. However, this join appears to be ineffective, as amounts aren't appearing for all weeks as expected.

In an effort to address this, I tried merging the two tables using Power Query. Unfortunately, using a Left join did not yield any matching records due to the weekly date folding. My objective is to ensure that all dates are displayed in correspondence with the respective weeks, where the WeekEndDate signifies the week's conclusion.

Could anyone offer any guidance or suggestions ?

[Sunila]

Hi, @v-xinruzhu-msft 

Unfortunately, I dont have the appropriate role to attach the pbi.

Here is screen shot of Table A and advance editor code

let
Source = {Int32.From(DateTime.LocalNow())-1..Int32.From(DateTime.LocalNow())+6*14+1},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.AddDays([Column1],6)),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each Date.DayOfWeek([Column1])=Date.DayOfWeek(DateTime.LocalNow())),
#"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Custom.1", each Date.ToText([Column1],[Format="dd/M"]) & "-" & Date.ToText([Custom],[Format="dd/M"])),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Column1", "WeekStartDate"}, {"Custom", "WeekEndDate"}, {"Custom.1", "Week"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"WeekStartDate", type date}, {"WeekEndDate", type date}})
in
#"Changed Type1"

Table B

Table A.WeekStartDate join Table B.Date (One to many)

Report View : Amount distribution is not appearing for some week buckets, Amount in 13 weeks bucket must add up to Totals in Right. 

Please note, report is designed to dynamically start from the current date. So, each week will kick off with today's date. Should have any questions please let me know. 

[v-xinruzhu-msft · Accepted solution]

Xinru Zhu 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sunila]

Thank you so much, your proposed solution did work.

[v-xinruzhu-msft]

Hi @Sunila 

Can you provide some more sample data or pbix file, I cannot get the information based on the picture you have provided.

Best Regards!

Yolo Zhu