Need Help: Create Calculated Table and Graph

[AroraPK]

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.

Task=50Units

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)

[AroraPK]

Replied below

[v-chenwuz-msft]

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.

[AroraPK]

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

[v-chenwuz-msft]

Hi @AroraPK ,

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

Best Regards

Community Support Team _ chenwu zhu