Skip to main content
cancel
Showing results for 
Search instead 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

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

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)

AroraPK_0-1658235199211.png

 

4 REPLIES 4
AroraPK
Regular Visitor

Replied below

v-chenwuz-msft
Community Support
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:

vchenwuzmsft_0-1658390280459.png

 

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.

 

 

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.
AroraPK_0-1658438622676.png

 

Data View
So that it can create graph like as on today:
AroraPK_1-1658438622570.png

 

Graph

Hi @AroraPK ,

 

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

 

Best Regards

Community Support Team _ chenwu zhu

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors