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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors