Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Planted_Baker
Frequent Visitor

Confused with Calculating Rolling Averages & Plotting them

Google Drive Link 

 

Dear All,Screenshot 2022-09-26 164244.png

I am new here as well as new to Power Bi.

I have learned the basics and have working level understanding of baisc excel and DAX formulas and associated syntax. I am trying to build a dashboard for my organization. Have tried everything with trial & error, looking up on the web but still am unable to figure out the way out. Please check the attached excel file (same file uploaded on Google Drive & One Drive).

 

I have a table (~ 1M rows) where each row represents one item. Each item is assoicated to

a) AREA - "1" or "3"

b) Conducted in - "S" - Shop or "F" - Field

c) Carried out by different Subcontractors - "A1", "A2", "A3"

 

The table has multiple date colums corrosponding to different activities on specific item (in some cases the activity is required, in some not). The activities are "X1", Y1" & "Z1".

 

I have created a date table separately which contains all unique dates and WEEKNUM. 1-to-many relationship has established the tables. 

 

I now intend to data Matrix visualition and line chart as shown in the table. The data is pulled on weekly basis. So KPIs are calculated on weekly totals and Cumulative (Rolling Sum) totals. Please help in any possible way. Also available for suggestions if the data can be presented in a better way.

 

PS: I have access to Office 365 & PRO license, so no limitations on any specific service. 

3 REPLIES 3
v-chenwuz-msft
Community Support
Community Support

Hi @Planted_Baker ,

 

Sorry, we have no access to the file from your link.

 

Best Regards

Community Support Team _ chenwu zhu

 

Hi Chenwu Zhu, thanks for response. I have created a new detailed post with .pbix file (https://community.powerbi.com/t5/Desktop/Running-Totals-and-Charts/m-p/2809790) for better understanding. Kindly help if you can on that post.

I have two tables below: Data Table & Date Table. In data table, each row is represented by a unique item. There are 3 activities (X1, Y1 & Z1) which get conducted on each item (a,b,c,d,e,...) on different dates Date_X1 for X1, Date_Y1 for Y1, etc. Date table lists all the dates in sequence with WeekNum.

I intend to get totals for X1, Y1 & Z1 on weekly basis as well as rolling weekly totals.

Relationship between Date[Date] & Data [Date_X1], Data [Date_Y1] & Data [Date_Z1] is created. Need to calculate Weekly Totals (Count = 1 for each row) based on dates for X1, Y1 & Z1.

|Data|X1|Date_X1|Y1|Date_Y1|Z1|Date_Z1|

:-:|:-:|:-:|:-:|:-:|:-:|:-:|

|a 

|||||||

|b|||||||

|c|||||||

|d|||||||

|e|||||||

|f|||||||

|g|||||||

|h|||||||

|i|||||||

 

|Date|WeekNum|

:--|:--|

|01/01/2022|1-2022|

|03/01/2022|1-2022|

|08/01/2022|2-2022|

|16/01/2022|3-2022|

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.