Forum Discussion

AroraPK's avatar
AroraPK
Regular Visitor
4 years ago

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 i...
Need Help
v-chenwuz-msft's avatar
v-chenwuz-msft
Icon for Community Support rankCommunity Support
4 years ago

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's avatar
AroraPK
Regular Visitor
4 years ago

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's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi AroraPK ,

     

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

     

    Best Regards

    Community Support Team _ chenwu zhu