cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Need Help: Create Calculated Table and Graph

I have a table with columns (Task, StartDate, EndDate) apart from other columns. Task is whole number unit to be completed on or before EndDate once started on StartDate. During the cource of "Task is In-Progress" I have to display graph for Traget vs Actual numbers.

StartDate = 14-Jul-2022, EndDate = 20-Jul-2022,          EndDate - StartDate = 5 business days (excluding weekends)

Daily Target = 50 / 5 = 10units/day

I have to display graph of Target & Actual and for that I am thinking to Create dynamic table like below and create graph for Traget vs Actual. Target should be drawn on the day-1 of task StartDate. Acutal execution / completion of teask numbers come from another table.

 Date Target Actual 14-Jul-2022 10 8 15-Jul-2022 10 9 16-Jul-2022 (Sat) 0 5 17-Jul-2022 (Sun) 0 0 18-Jul-2022 10 11 19-Jul-2022 10 20-Jul-2022 10

Testing Start and Testing End are in a row. I get total task from test_testplan and daily completion from testrun_testexcn (based upon finishedon)

4 REPLIES 4
Regular Visitor

Replied below

Community Support

Hi @AroraPK

Please create a summarize table by crossjoin() function.

``summarize table = filter(CROSSJOIN('calendar','ProjectTable'),[Date]>=[start]&&[Date]<=[end])``

Then add one more column in this table.

``Target = if(WEEKDAY([Date],2)>5,0,[Task]/NETWORKDAYS([start],[end]))``

And for actual, create a measure like following:

``````actual =
CALCULATE (
SUM ( 'Actual table'[actual] ),
FILTER (
'Actual table',
[Projectid] IN VALUES ( 'summarize table'[Projectid] )
)
)
``````

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regular Visitor

Thanks for guidance... The only deviation in your exmple and what is expected is

Suppose there is no actual count on particular dates... and also we haven't reached to end date of task yet.

Data View
So that it can create graph like as on today:

Graph
Community Support

Hi @AroraPK ,

Please share you pbix file without sensitive data and expect result than a screeshot.

Best Regards

Community Support Team _ chenwu zhu