Summing data with different date frequencies

[andy_scott42]

I have some sales data from a Snapshot Fact tale aggregating some sales data for different sites.

However, the frequency of the data I receive from the sites is different i.e. some are daily and some are weekly. See below example:

On the 22nd I recieved sales data from Sites D,E,F,G and H making a total of 8248.

How can I include the weekly sales in the future dates until the next weekly data comes in. For example I want to display 8335 (G13 in the photo) as my total sales for the 23rd.

I tried writing a cumulative sum in dax but I still ended up with a line like this 

Also the data already is cumulative as its from a Snapshot Fact table.

Thanks

[andy_scott42 · Accepted solution]

The solution I came up with was to add a Row Number to the data based on WeekDay desc, so for each site I could filter to show the latest data per site per week. I then changed the axis of my chart to weekly, showing the first date of each week.

[VijayP]

@andy_scott42 

Calculate(MAX(amount),all(Table),Values(Site)) Try this

[VijayP]

@andy_scott42 

Calculate(sum(sales),Filter(All(DateDim),DateDim[Date]<=MAx(DateDim[Date]))

This measure will give the cumulative effect. you need to create a Date Dimension Table and connect with FAct Table with DAte. 

How to Create a Date Table - https://www.youtube.com/watch?v=C3ckN6LN6xo

Watch the video

[andy_scott42]

Thanks for the speedy reponse Vijay but just doing a cumulative sum doesn't meet the requirement as the data is already aggregated daily. Your solution double counts the number of sales.

[negi007]

@andy_scott42  Since you have date in your table, you can easily create a week column. So when you get daily data it will have the date for that day and for weekly sales you put the sales number under any of the day during the week. This way your weekly column will help you in giving you weekly sales total which you are chasing in this case. 

let me know if it works for you. You can always share your data in text or powerbi format to assist you better.

[andy_scott42]

Thanks. I added a FirstOfWeek column from my date dimension but it summed up all the values for the week. As the data is already aggregated in the snapshot Fact all I really need is the max value for each site per week.

[v-kelly-msft]

Hi @andy_scott42 ,

Could you pls advise me how to get the result of 8335?BTW,could you paste your data and copy it here rather than showing the screeshot?

I would try my best to find the solution you need.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!