[HELP] Breaking Inicial Date and Final Date into various date lines in Table Model

[danilindoso]

Hello Everyone, first post here in the community. I hope to be as clear as possible.

I work in the marketing departament and I created a Google Sheets to put all of our products campaign. So each line will have differents products campaign, and there two important columns that show the initial date campaign and the final date campaing. Which means, it will only consider all the purchases of this item in between those two dates.

So I connected this Google Sheets in my Power BI Desktop, it looks a litlle bit like this in Power Query: 

I already have in my Power BI, my transactions datebase, so this Google Sheets will have to conect to my database, to actually show me how many purchases I had for each one of my products campaign in that specific date range (initial and final date).

So I made a conection to the Product ID of my database with this Product ID of the Google Sheets.

But my biggest problem here, is the date part. My transaction database is based on, whenever I have a purchase, it will create a purchase line, and I need to see per day, how many purchases for each product campaing that is only in that specific date range.

I even thought about trying to break in to lots of lines like this:

If a product campaing will start 01/01/2023 and will finish 10/01/2023, it will break into 10 lines for each day between, and repeat the name of the campaing product. but even that I really don't know how to do it.

Doe anyone have any idea how to crack this?

Thank you very much in advance.

[danilindoso]

Here is the PBI File.

We have 4 tables:

- Database orders (that creates line for each purchase)

- Database Products_Sold (To conect with table orders and see what are the products sold)

- Store (To see the name of the stores that sold the products)

- Products_Campaign (This is the google sheets database that I put every single product campaign)

https://drive.google.com/file/d/1kJzJo_QwokYNdEuVx6BVZazpZwCM5v_4/view?usp=share_link 

[adudani · Accepted solution]

Hi @danilindoso,

Create a new custom column and try this:

List.Dates([Initial Date],[Final Date]-[Initial Date]+1,#duration(0,0,0,1)).

expand the column. 

Please let me know if this resolved the question.

Appreciate a thumbs if this is helpful.

[danilindoso]

It didn't work :(, it says something about the operation "+" and the #duration is not valid.

Can I attached a File so you can take a look at it?

As a result, I need to check per day, how many campaing products were sold. But it has to consider only the between initial date and final date from each product.