cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ashwinpal Frequent Visitor
Frequent Visitor

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

Re: Need help with date and week combo

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.

Ashwinpal Frequent Visitor
Frequent Visitor

Re: Need help with date and week combo

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

Super User II
Super User II

Re: Need help with date and week combo

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 ?

Ashwinpal Frequent Visitor
Frequent Visitor

Re: Need help with date and week combo

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.

Ashwinpal Frequent Visitor
Frequent Visitor

Re: Need help with date and week combo

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.

Super User II
Super User II

Re: Need help with date and week combo

concatenate the week with the year: 2019-41

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors