cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
andy_scott42
Helper II
Helper II

Summing data with different date frequencies

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:

 

andy_scott42_0-1613146287807.png

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 

andy_scott42_1-1613146586107.png

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

 

Thanks

1 ACCEPTED SOLUTION
andy_scott42
Helper II
Helper II

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.

View solution in original post

7 REPLIES 7
andy_scott42
Helper II
Helper II

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.

View solution in original post

VijayP
Super User I
Super User I

@andy_scott42 

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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


VijayP
Super User I
Super User I

@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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


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

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




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


Proud to be a Super User!

Follow me here


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.

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!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.