Measure to calculate sum of week start date project wise

[shefalinishad11]

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

[Jimmy801 · Accepted solution]

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

[shefalinishad11]

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?

[Jimmy801]

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

[shefalinishad11]

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.

[Jimmy801]

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

[shefalinishad11]

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.