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 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.

 

DateTargetActual
14-Jul-2022108
15-Jul-2022109
16-Jul-2022 (Sat)05
17-Jul-2022 (Sun)00
18-Jul-20221011
19-Jul-202210 
20-Jul-202210 

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)

 

Need Help

4 Replies

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community 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
        Community 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