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.
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.
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.
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 ?
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.