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
Anonymous
Not applicable

Need help with date and week combo

I have a dataset which has the sales and date of sale. I am trying to plot a graph bw sales and date of sale and obtain a trend from it. However, some weeks have cumulative sales for a week and some have for all days or few days of a week. How can I merge the data and plot a graph for the past 5 years. Currently, the graph reaches a peak where there is just sales calculated for a week. It should be evenly distributed through the whole week, or the whole data needs to have an amount on a weekly basis. Thank you. Please help.

 

6 REPLIES 6
kentyler
Solution Sage
Solution Sage

You need an intermediate step where your data has sales for a day. If you only have sales for the week you could distribute 1/7 to each day. You say some of your data is already sales per day, so you would have to separate the data that is sales per week and generate sales per day from then, then combine it back with the data that already shows sales per day.





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Anonymous
Not applicable

Thank you @kentyler . The data set is huge and has dates from 5 years or more. The sales per day are also not consistent; sometimes a day or two(or more) are missed. Is there a function in power BI to accumulate all the sales in a weekly manner based on the facilities. The sales are calculated based on facilities. The current trend graph looks like this for data after 2018. The bigger triangles towards the end are missing data for 6 days in a week.pbi.PNG

Can you aggregate the date by week before you try and load it ? maybe in sql server ?

If not you can write a measure to extract the week number for any date and then another that aggregates by week inside of power bi. Can you cut down the dataset to a few hundred rows and send it to me in an excel file ?





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Anonymous
Not applicable

The week number seems to do the job for one particular year, however, it starts from one the next year onwards. Is there a function or short method to accumulate the week numbers in sequence (53 , 54, ... and so on). Thank you.

concatenate the week with the year: 2019-41

you will have to do it as strings not numbers that are added together





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Anonymous
Not applicable

I think I can try using the measure inside Power BI to find the week number. I am not allowed to share the dataset. Thank you.

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.