Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
danilindoso
Regular Visitor

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

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: 

danilindoso_0-1674585761127.png

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.

1 ACCEPTED SOLUTION
adudani
Super User
Super User

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.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

View solution in original post

3 REPLIES 3
danilindoso
Regular Visitor

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
Super User
Super User

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.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

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.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors