Getting one column out of two date columns where the 1st one indicates the start and 2nd one end

[Anonymous]

Hi Dear Community experts,

I have the following issue of creating Power BI alternative of Excel function:

2 columns with start and end of campaigns are given and the budget of the campaign. Based on that information the daily spending is calculated.

The goal is to transform that 4 column table to a table with two columns: date column which will be conditionally linked to start and end of campaign (start date<= DATE <=end date) and respective total sum of all spendings od all campaigns for given day. 

Please check the attached screenshot which represents the issue. 

1

Will be very grateful for any advice/opinion you could share on this situation. 

Cheers, 

Saro

[amitchandak]

@Anonymous , refer if one the two can help

https://www.dropbox.com/s/yuv64v0cneseghx/value%20Split%20between%20months%20start%20end%20date.pbix?dl=0

https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0

[Fowmy]

Hi@Anonymous 
Are you looking for a Power Query solution?

[Anonymous]

Hi @Fowmy ,

thanks for reply. I need this in Power BI (NOT in Excel). So wherever that transformation can be done (at data tranasform stage or  as a measue) will be acceptable for me.  

[Fowmy]

@Anonymous 

Please use this power query solution. Assuming your data table name is CostData, you get the right output as desired.

let
    Source = Excel.CurrentWorkbook(){[Name="CostData"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Dates", each {Number.From([Start Date])..Number.From([End Date])}),
    #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom", "Dates"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Dates"}, {{"Cost Per Day Amount", each List.Sum([Cost Per Day]), type number}})
in
    #"Grouped Rows"

[Anonymous]

Dear @Fowmy, 

Thanks  so much for all efforts and sending this great solution. Do I understand correctly, this is a transformation, which is applied in Excel Power Query? 

If yes, that was not exactly what I was looking for. 

If it can be used in Power BI at which stage of data transformation and how I need to apply it, if my Excel file is already uploaded to Power BI desktop?

Many thanks again!

[Fowmy · Accepted solution]

@Anonymous 

In Power BI, once you get the data from Excel into in Power query Window, Go to Advanced Editor and paste below code,. Make adjustments to the path of the file and table name. (my table is CostData in the example)

let
    Source = Excel.Workbook(File.Contents("C:\Users\Fowmy\Downloads\File0088.xlsx"), null, true),
    CostData_Table = Source{[Item="CostData",Kind="Table"]}[Data],
    #"Added Custom" = Table.AddColumn(CostData_Table, "Dates", each {Number.From([Start Date])..Number.From([End Date])}),
    #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom", "Dates"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Dates"}, {{"Cost Per Day Amount", each List.Sum([Cost Per Day]), type number}})
in
    #"Grouped Rows"

https://1drv.ms/u/s!AmoScH5srsIYgYFf1P-rh0gnwwaW6A?e=U9Mwft

[lance_6]

This is obviously almost a year since this post, but I have a question regarding your syntax. 
What is the benefit of using 

{Number.From([Start Date])..Number.From([End Date])}

rather than a List.DateTimes function? 

[Fowmy]

@lance_6 

I am creating a list of dates, when you create a list with { }, it needs numbers here so I convert the dates to number from Stsrt and End dates.

List.DateTime can also do the job but is lengthy.