cancel
Showing results for
Did you mean:
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:

 1 Project ID Project Name Employee ID Employee Name WeekEnd Date Actuals 2 Project ID1 Project Name1 Employee ID1 Employee Name1 04-12-2020 2 3 Project ID2 Project Name2 Employee ID1 Employee Name1 11-12-2020 4 4 Project ID3 Project Name3 Employee ID1 Employee Name1 18-12-2020 4

Estimate:

Week Start Date

 1 Project ID Project Name Employee ID Employee Name 07-12-2020 14-12-2020 21-12-2020 28-12-2020 2 Project ID1 Project Name1 Employee ID1 Employee Name1 2 2 2 2 3 Project ID2 Project Name2 Employee ID1 Employee Name1 2 2 2 2 4 Project ID3 Project Name3 Employee ID1 Employee Name1 4 4 4 4

1 ACCEPTED SOLUTION
Community Champion

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

6 REPLIES 6
Community Champion

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

Helper I

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?

Community Champion

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

Helper I

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.

Community Champion

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

Helper I

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.

Announcements

#### Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

#### Power BI May 2023 Update

Find out more about the May 2023 update.