Create a new table to obtain daily target

[wmf_07]

I have a table with monthly targets by branch and category for each month. Is it possible to create a table that includes all the month dates with its daily target by branch and category? 

Daily Target = Target / No.of days in that month.

The reason I want it as a table and not a measure is because in my sales data when the agent has not made a sale on 1 day it does not show his daily sale vs daily target. eg. if the agent has not made a sale on 3rd Jan in category A it does not show Daily sale vs target ( 0 vs 26)

The data I have :

Month	Branch	Category	Monthly Target
Jan	AX	A	800
Jan	BC	B	500
Feb	AX	A	750
Feb	BC	B	550

Desired Output :

Date	Branch	Category	Daily Target
01-01-2024	AX	A	26
02-01-2024	AX	A	26
03-01-2024	AX	A	26
04-01-2024	AX	A	26
05-01-2024	AX	A	26
06-01-2024	AX	A	26
07-01-2024	AX	A	26
08-01-2024	AX	A	26
09-01-2024	AX	A	26
10-01-2024	AX	A	26
11-01-2024	AX	A	26
12-01-2024	AX	A	26
13-01-2024	AX	A	26
14-01-2024	AX	A	26
15-01-2024	AX	A	26
16-01-2024	AX	A	26
17-01-2024	AX	A	26
18-01-2024	AX	A	26
19-01-2024	AX	A	26
20-01-2024	AX	A	26
21-01-2024	AX	A	26
22-01-2024	AX	A	26
23-01-2024	AX	A	26
24-01-2024	AX	A	26
25-01-2024	AX	A	26
26-01-2024	AX	A	26
27-01-2024	AX	A	26
28-01-2024	AX	A	26
29-01-2024	AX	A	26
30-01-2024	AX	A	26
31-01-2024	AX	A	26

[Alex87 · Accepted solution]

Hello,

You need your months to be Dates. You can use the following M code :

let
 Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PK8lQ0lFyKkrMSwYxnBNLUtPziyqBTLBcTqVCSGJRemqJUqxOtJKBoT4QGRkYmQDlHSNABBBbGBhgyjo5gwggNkWSNcLQa26KRRahFyQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Month", type date}, {"Monthly Target", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Month", "StartDate"}}),
    EndDate = Table.AddColumn(#"Renamed Columns", "EndDate", each Date.EndOfMonth([StartDate]), type date),
    List = Table.TransformColumnTypes(
Table.AddColumn(
            Table.AddColumn(EndDate, "Date", each { Number.From([StartDate])..Number.From([EndDate]) } 
            ), "Temp", each List.Count( [Date] ) 
        ),
{{"Temp", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(List, "MonthlyTargt", each [Monthly Target]/[Temp]),
    ChType = Table.TransformColumnTypes(#"Added Custom",{{"MonthlyTargt", Int64.Type}}),
    Scope = Table.SelectColumns(ChType,{"Branch", "Category", "Date", "MonthlyTargt"}),
    ExpandDates = Table.TransformColumnTypes( Table.ExpandListColumn(Scope, "Date"), {{"Date", type date}}) //Expand and assign type to the date column
in
    ExpandDates

If it answers your query, please mark my reply as the solution. Thanks!

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}, {"Branch", type text}, {"Category", type text}, {"Monthly Target", Int64.Type}}),
    #"Inserted Days in Month" = Table.AddColumn(#"Changed Type", "Daily target", each [Monthly Target]/Date.DaysInMonth([Month]), type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Days in Month",{"Monthly Target"}),
    #"Inserted End of Month" = Table.AddColumn(#"Removed Columns", "Date", each {Number.From([Month])..Number.From(Date.EndOfMonth([Month]))}),
    #"Expanded List" = Table.ExpandListColumn(#"Inserted End of Month", "Date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded List",{{"Date", type date}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Month"})
in
    #"Removed Columns1"

Hope this helps.

[Alex87 · Accepted solution]

Hello,

You need your months to be Dates. You can use the following M code :

let
 Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PK8lQ0lFyKkrMSwYxnBNLUtPziyqBTLBcTqVCSGJRemqJUqxOtJKBoT4QGRkYmQDlHSNABBBbGBhgyjo5gwggNkWSNcLQa26KRRahFyQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Month", type date}, {"Monthly Target", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Month", "StartDate"}}),
    EndDate = Table.AddColumn(#"Renamed Columns", "EndDate", each Date.EndOfMonth([StartDate]), type date),
    List = Table.TransformColumnTypes(
Table.AddColumn(
            Table.AddColumn(EndDate, "Date", each { Number.From([StartDate])..Number.From([EndDate]) } 
            ), "Temp", each List.Count( [Date] ) 
        ),
{{"Temp", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(List, "MonthlyTargt", each [Monthly Target]/[Temp]),
    ChType = Table.TransformColumnTypes(#"Added Custom",{{"MonthlyTargt", Int64.Type}}),
    Scope = Table.SelectColumns(ChType,{"Branch", "Category", "Date", "MonthlyTargt"}),
    ExpandDates = Table.TransformColumnTypes( Table.ExpandListColumn(Scope, "Date"), {{"Date", type date}}) //Expand and assign type to the date column
in
    ExpandDates

If it answers your query, please mark my reply as the solution. Thanks!

[DataNinja777 · Accepted solution]

Hi @wmf_07 

There are many ways to achive your requried output and one of them is creating a crossjoin of your table and calendar table and filtering out the non-existent data rows in the combined table. 

First, crossjoin the calendar table with day granularity and your table with monthly target.  

Then, if there is no monthly target data in the original table, delete the rows in the 2nd calculated table.  

Your required output is produced in 'Table 2' as shown below:

I attach the example pbix file.  The above method is probably involving more steps than what is possible and there maybe more simple and elegant ways to do it using dax, but please let me know if this meets your requirement. 

Best regards,