Distribute monthly value to daily

[vivran22]

Hey community members!

Need help.

I have a monthly table for target:

Month	Target
Jan-20	11
Feb-20	11
Mar-20	7
Apr-20	6
May-20	6
Jun-20	17
Jul-20	10
Aug-20	10
Sep-20	10
Jan-20	57
Feb-20	45
Mar-20	43
Apr-20	36
May-20	36
Jun-20	12

And then a daily table for actual:

Date	Actual
1-Jan-20	2
2-Jan-20	2
3-Jan-20	2
4-Jan-20	3
5-Jan-20	3
6-Jan-20	3
7-Jan-20	4
1-Feb-20	6
2-Feb-20	10
3-Feb-20	12
4-Feb-20	9
5-Feb-20	8
6-Feb-20	7
7-Feb-20	5
8-Feb-20	12
10-Mar-20	10
11-Mar-20	4
12-Mar-20	4
13-Mar-20	4
14-Mar-20	4
15-Mar-20	3
16-Mar-20	3
17-Mar-20	3

Required visual:

The requiremnt is to compare the monthly values with daily values:

The solution could be simple one, but unable to wrap my head around it 😞

[BA_Pete]

Hi @vivran22 

Add a combined column to your target table that includes all the dimensions that you want to be able to filter on, as well as month:

// Call this "targetTable" so the join works with next table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krM0zUyUNJRcgRiQ0OlWJ1oJbfUJAwx38QihJg5WMixAEnIDKqqEl3IqxTZAnOoWA6SmAHEtNJ0DLHg1AIMMbiDnYDY1BzVwSAxE1NUB4PFjFFdDBIzRnMykhjczSAxQyOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Location = _t, Target = _t]),
    addLocationMonth = Table.AddColumn(Source, "locationMonth", each Text.Combine({[Location], [Month]}, "-"), type text)
in
    addLocationMonth

Then add the same combined column to your actual table and join on this column:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdExDsIwDEbhu2RupMZ2kq4wMCBxgqhDkVgZuP8AqkCpyRu8fNMvv9ZCitftGWUOUzh9TsI6tSCESmgedcdMWAirR9sxxcvj/o9CqIRGmAmLx98kwIUwzfG2vYb1CVVQFdVQs9fvhoJaUfuzz5S6oxKaxyF1x0JYPQ6pOwqhEhphJiweh9QdF8Jj6sP6hCqoimqo2euY+qB10PUN", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Location = _t, Actual = _t]),
    addLocationMonth = Table.AddColumn(Source, "locationMonth", each Text.Combine({[Location], Text.End([Date], 6)}, "-"), type text),
    mergeTargetTable = Table.NestedJoin(addLocationMonth, {"locationMonth"}, targetTable, {"locationMonth"}, "targetTable", JoinKind.LeftOuter),
    expandTargetTable = Table.ExpandTableColumn(mergeTargetTable, "targetTable", {"Target"}, {"Target"})
in
    expandTargetTable

This should give you table that will do want you need.

Pete

[CNENFRNL]

Hi, @vivran22 , how about averaging monthly target according to days in the daily table? e.g. Jan-20, 11/7; Feb-20, 11/8