Timeline Calculaction

[Anonymous]

Hi to All,

I am workinng on a dashboard where i need timeline

My total working day in a month 25

Passed day in a month 24 (excludes weekend and holidays) as per Today

Timeline =96%

what i want i'll tell you guys

today my Timeline will looks like 'Timeline = Time Elapsed : 96% (24 of 25 Working Days)' 

Time line table for your reference

Pls help me 

Day Name	InvDate	TotalWorkingDay	DayRemain	DayPassed	Timeline%	Time Elapsed
Wed	6/1/2022	25	25	1	4%	Time Elapsed : 4% (1 of 25 Working Days)
Thu	6/2/2022	25	24	2	8%	Time Elapsed : 8% (2 of 25 Working Days)
Fri	6/3/2022	25	23	3	12%	Time Elapsed : 12% (3 of 25 Working Days)
Sat	6/4/2022	25	22	4	16%	Time Elapsed : 16% (4 of 25 Working Days)
Sun	6/5/2022	25	22	4	16%	Time Elapsed : 16% (4 of 25 Working Days)
Mon	6/6/2022	25	21	5	20%	Time Elapsed : 20% (5 of 25 Working Days)
Tue	6/7/2022	25	20	6	24%	Time Elapsed : 24% (6 of 25 Working Days)
Wed	6/8/2022	25	19	7	28%	Time Elapsed : 28% (7 of 25 Working Days)
Thu	6/9/2022	25	18	8	32%	Time Elapsed : 32% (8 of 25 Working Days)
Fri	6/10/2022	25	17	9	36%	Time Elapsed : 36% (9 of 25 Working Days)
Sat	6/11/2022	25	16	10	40%	Time Elapsed : 40% (10 of 25 Working Days)
Sun	6/12/2022	25	16	10	40%	Time Elapsed : 40% (10 of 25 Working Days)
Mon	6/13/2022	25	15	11	44%	Time Elapsed : 44% (11 of 25 Working Days)
Tue	6/14/2022	25	14	12	48%	Time Elapsed : 48% (12 of 25 Working Days)
Wed	6/15/2022	25	13	13	52%	Time Elapsed : 52% (13 of 25 Working Days)
Thu	6/16/2022	25	12	14	56%	Time Elapsed : 56% (14 of 25 Working Days)
Fri	6/17/2022	25	11	15	60%	Time Elapsed : 60% (15 of 25 Working Days)
Sat	6/18/2022	25	10	16	64%	Time Elapsed : 64% (16 of 25 Working Days)
Sun	6/19/2022	25	10	16	64%	Time Elapsed : 64% (16 of 25 Working Days)
Mon	6/20/2022	25	9	17	68%	Time Elapsed : 68% (17 of 25 Working Days)
Tue	6/21/2022	25	8	18	72%	Time Elapsed : 72% (18 of 25 Working Days)
Wed	6/22/2022	25	7	19	76%	Time Elapsed : 76% (19 of 25 Working Days)
Thu	6/23/2022	25	6	20	80%	Time Elapsed : 80% (20 of 25 Working Days)
Fri	6/24/2022	25	5	21	84%	Time Elapsed : 84% (21 of 25 Working Days)
Sat	6/25/2022	25	4	22	88%	Time Elapsed : 88% (22 of 25 Working Days)
Sun	6/26/2022	25	4	22	88%	Time Elapsed : 88% (22 of 25 Working Days)
Mon	6/27/2022	25	3	23	92%	Time Elapsed : 92% (23 of 25 Working Days)
Tue	6/28/2022	25	2	24	96%	Time Elapsed : 96% (24 of 25 Working Days)
Wed	6/29/2022	25	1	25	100%	Time Elapsed : 100% (25 of 25 Working Days)
Thu	6/30/2022	25	0	26	104%	Time Elapsed : 104% (26 of 25 Working Days)

[v-cgao-msft · Accepted solution]

Hi @Anonymous ,

Please create these calculated columns.

Month = 
MONTH ( 'Table'[Date] )

WorkDay = 
IF ( WEEKDAY ( 'Table'[Date], 2 ) = 7 , 0, 1 )

TotalWorkingDay = 
25

DayPassed = 
COUNTX (
    FILTER (
        'Table',
        'Table'[WorkDay] = 1
            && 'Table'[Month] = EARLIER ( 'Table'[Month] )
            && 'Table'[Date] <= EARLIER ( 'Table'[Date] )
    ),
    'Table'[Date]
)

DayRemain = 
'Table'[TotalWorkingDay] - 'Table'[DayPassed] + 1

Timeline% = 
DIVIDE ( 'Table'[DayPassed], 'Table'[TotalWorkingDay] )

The PBIX file is attached for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Anonymous]

TimeElapsed should come like if my InvDate is this(6/28/2022) than Than TimeElapsed will this (Time Elapsed : 96% (24 of 25 Working Days)

[Anonymous]

allexcept(Table, Table[Month Year]) )

do i need to add a coulmn in my table for Month year

[amitchandak]

@Anonymous , You need to have working flag

example

workday column

if(weekday([Date], 2) =1, 0,1)

Measures

days=  countx(filter(Table, Table[workday] =1), [Date])

days in month =  calculate(countx(filter(Table, Table[workday] =1), [Date]), allexcept(Table, Table[Month Year]) )

then you can divide two