cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
bvss Member
Member

How to show the network days between end date and latest fiscal year

Hi Community,

 

I have created one measure to find out network days between start date and end date. See below:

Network Days =
VAR Calendar1 = CALENDAR(MIN('Query1'[Start]),MAX('Query1'[End]))
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<=6),[Date]) - COUNTX(FILTER(Calendar2,[WeekDay]>=6),[Date])
 
But, I want to show the network days between start date of latest fiscal year and end date
for ex: if start is 11/23/2012 and End is 1/7/2019 so then i wnat network days between 04/01/2019 to 01/07/2019
and some them are having start and end days are in same fiscal year. 
That's why not able to show the network days for latest fiscal year
 
Below is the data
 
Item DescriptionFY Year Start for StartStartFY Year Start for EndEndNetworkdays for Start and End FYEND for EndNetWorkDays B/W End and Fy EndTerms - PriceCancelled Revenue
+4/1/201811/19/20184/1/20181/31/2019443/31/201933$65$17,160
Adam Etman4/1/20187/5/20184/1/20181/4/20191063/31/201948$80$30,720
Adashea Simpson-Womack4/1/201610/4/20164/1/20182/6/20194903/31/201930$22$5,280
Aditya Malhotra4/1/20182/5/20194/1/20194/22/2019443/31/2020198$50$79,200
Alan Woo4/1/20111/23/20124/1/20181/7/201914533/31/201948$79$30,336
Alejandro G Villegas4/1/20175/1/20174/1/20182/1/20193693/31/201932$88$22,528
Alexander Gallo4/1/201811/19/20184/1/20181/11/2019333/31/201944$50$17,600
Alexandra Garret4/1/20185/14/20184/1/20195/24/20192173/31/2020178$50$71,200
Ali Khomusi4/1/20186/18/20184/1/20196/4/20192023/31/2020173$79$109,336
Alina Collins4/1/20186/25/20184/1/20182/5/20191303/31/201931$85$21,080
Alok Tandon4/1/201411/3/20144/1/20182/28/20199043/31/201917$60$8,160
Alton Harmon4/1/201712/5/20174/1/20196/7/20193163/31/2020170$55$74,800
 
Can you guys please help me out from this.
 
Thank you in advance
 
B V S Sudhakar
1 ACCEPTED SOLUTION

Accepted Solutions
tex628 New Contributor
New Contributor

Re: How to show the network days between end date and latest fiscal year

 

Network Days, Both = 
VAR start_ = SELECTEDVALUE('Query1'[Start])
VAR FY_ = SELECTEDVALUE('Query1'[FY Year Start for End])
VAR Calendar1 = CALENDAR(MIN('Query1'[FY Year Start for End]),MAX('Query1'[End Date]))
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
VAR Calendar3 = CALENDAR(MIN('Query1'[Start ]),MAX('Query1'[End Date]))
VAR Calendar4 = ADDCOLUMNS(Calendar3,"WeekDay",WEEKDAY([Date],2))
RETURN 
IF(start_>FY_ ,
COUNTX(FILTER(Calendar4,[WeekDay]<=5),[Date]) ,
COUNTX(FILTER(Calendar2,[WeekDay]<=5),[Date]))

 

 

12 REPLIES 12
bvss Member
Member

Re: How to show the network days between end date and latest fiscal year

I tried to create network days between (FY Year Start for End)  and (end) 

But some values are not coming properly

 

Ex: 1st in the data       

Start FY Year Start for EndEnd DateNetworkdays for Start and End
11/19/20184/1/20181/31/201944

But If i try to find network days between (FY Year Start for End)  and (end) it is giving apprx.175 it's wrong

 

One more is

Start  FY Year Start for EndEnd DateNetworkdays for Start and End
1/23/20124/1/20181/7/20191453

If i try to find network days between (FY Year Start for End)  and (end) it is giving apprx.165 it's correct

 

I am not able create proper measure

 

Please help me out from this

 

Thanks in advance

 

B V S Sudhakar

 

bvss Member
Member

Network Days Issue

Hi Community people,

 

Please help me out from this which i posted in below link and i elobarated with example in reply..

 

https://community.powerbi.com/t5/Desktop/How-to-show-the-network-days-between-end-date-and-latest-fi...

 

Hi @tex628  Please have look for this

 

 

Thanks in advance

 

B V S Sudhakar

 

bvss Member
Member

Re: How to show the network days between end date and latest fiscal year

Hello,

 

Any Suggestions...

Is it possible

 

 

Thanks & Regards,

B V S Sudhakar

tex628 New Contributor
New Contributor

Re: How to show the network days between end date and latest fiscal year

Hello @bvss

In your calculation,

Network Days =
VAR Calendar1 = CALENDAR(MIN('Query1'[Start]),MAX('Query1'[End]))
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<=6),[Date]) - COUNTX(FILTER(Calendar2,[WeekDay]>=6),[Date])

 I'm assuming that what you are aiming for is calculating the difference in workdays between [FY Year Start for End] and [End Date]. This would mean that you count the total amount of days and exclude sundays & saturdays. From what i can see you need to change the <=6 to a <=5 as you are counting sundays twice! 

But whats confusing me is that you're saying that 175 is the correct number of days between the 4/1/2018 - 1/7/2019 ... Did i miss something maybe? 

bvss Member
Member

Re: How to show the network days between end date and latest fiscal year

Hi @tex628 ,

 

I said approx. value.. and i changed in my measure. It's giving proper value for same fiscal year of start and end.

i.e.

Start FY Year Start for EndEnd DateNetworkdays for Start and End
11/19/20184/1/20181/31/201934

 It's correct but at the same time i need to show

network days between (FY Year Start for End) and (End) 

 

Start  FY Year Start for EndEnd DateNetworkdays for Start and End
1/23/20124/1/20181/7/20191453

 

Can you please help me for this also

 

Thank you in advance

 

tex628 New Contributor
New Contributor

Re: How to show the network days between end date and latest fiscal year

I'm not entirely sure that i understand you totally, im sorry! 

But going to try and simplify this as much as possible.

Here are your examples:
image.png

I used your measure to make 2 new ones:

Network Days, Start -> End = 
VAR Calendar1 = CALENDAR(MIN(Table3[Start ]),MAX(Table3[End Date]))
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<=5),[Date])
Network Days, FY -> End = 
VAR Calendar1 = CALENDAR(MIN(Table3[FY Year Start for End]),MAX(Table3[End Date]))
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<=5),[Date])

Which gave me this result: 
image.png

Does the numbers in tehse two measures appear to be correct? 

/J

bvss Member
Member

Re: How to show the network days between end date and latest fiscal year

Hi @tex628 ,

 

But I want to show those two correct values in one column only means i need to show 201 and 54 in one column

 

Can you please give some suggetion / Solution

 

 

Thank you in advance

 

tex628 New Contributor
New Contributor

Re: How to show the network days between end date and latest fiscal year

Both in the same column, like this? 
image.png

bvss Member
Member

Re: How to show the network days between end date and latest fiscal year

Hi @tex628 

 

No, We need to show only 201 and 54.

 

I don't know is it possible are not.. 

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 312 members 3,440 guests
Please welcome our newest community members: