cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shefalinishad11
Helper I
Helper I

Measure to calculate sum of week start date project wise

Hi Guys,

 

I am working to build a dashboard, and I got stuck. I want to put a Project level filter to show the weekly total estimate to the weekly actual in the one graph.  

The weekly actuals come in rows and the estimate is in the column with the project name in a row. I try to calculate it with sumx function but it didn't work. I am not able to share actual data with you guys but really hope this hypothetical work.

 

Filter: Project Name (Done)

Graph: to show a graph of the weekly trend  with weekly (Monday(Estimate) to Actual)

 

Actuals:

 

1Project IDProject NameEmployee IDEmployee NameWeekEnd DateActuals
2Project ID1Project Name1Employee ID1Employee Name104-12-20202
3Project ID2Project Name2Employee ID1Employee Name111-12-20204
4Project ID3Project Name3Employee ID1Employee Name118-12-20204

 

Estimate: 

                                                                                                            Week Start Date

1Project IDProject NameEmployee IDEmployee Name07-12-202014-12-202021-12-202028-12-2020
2Project ID1Project Name1Employee ID1Employee Name12222
3Project ID2Project Name2Employee ID1Employee Name12222
4Project ID3Project Name3Employee ID1Employee Name14444

 

1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

Hello @shefalinishad11 

 

you have to normalize your estimate table before and bring the date-values that are in columns to row level. This you can achive in PowerQuery with Table.UnpivotOtherColumns. After that you may need to adapt the dates as well (calculating week start from the week end-days or to add a calcuated column that gives you week number and year.


If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

6 REPLIES 6
Jimmy801
Community Champion
Community Champion

Hello @shefalinishad11 

 

you have to normalize your estimate table before and bring the date-values that are in columns to row level. This you can achive in PowerQuery with Table.UnpivotOtherColumns. After that you may need to adapt the dates as well (calculating week start from the week end-days or to add a calcuated column that gives you week number and year.


If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

Hey @Jimmy801 

Thanks, it worked really well. I got all the values in a row.

 

I have one more query here... I created a measure calendar table and link to try to create a connection between the Actual table  (weekend) and Estimate (Weekend), show that I can put the graph with a week number & week start date. But I am not able to do so. Is there any way around it?

Hello @shefalinishad11 

 

create a calendar table in DAX or Power Query where you add the weeknumber/year. Then connect both of your tables to this calendar table. Now you can use the Weeknumber/year in the date-dimension to bring together your tables.

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Hey @Jimmy801 ,

 

While creating a relationship between Calendar to Estimate and Current table, I am able to create a relation between current and calendar but when I try to do for Estimate and calendar. it showing the below error.

 

 

Errors.jpg

 

 

 

Hello @shefalinishad11 

 

this is a topic of your datamodel. Seems there are other relationships that make this relationship impossible to make. But without any knowlegdge of your datamodel I can't give any support

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Hey @Jimmy801 ,

 

I cannot share my data and I am working on a budget vs the actual dashboard:

 

1. I have 2 Files - The estimated file(Yearly data) and the Actual File(update weekly). They both have the same format I shared above.

2. I have 1 more file, which has a yearly budget as per the project code.

 

What I have in mind to prepare:

 

> To create Budget vs actual data as per project and employee to show variance with every week between estimate to actual  (graph and table vise).

> Overall project health and status.

> Project trend from start to end when we select the project from the filter and same for an employee.

> Week Start date has an estimate and Week-end date have actuals.

> Line Graph showing a trend and X axis have a week start date and week-end date.

 

Table I am using right now:

  1. Estimate table
  2. Actual Table
  3. Budget Table
  4. Customize Calendar Table.

 

 

 

 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors