Calculate positive and Negative WorkingDays

[Mohan221213]

Hello All,

I am new to PowerBI and DAX, So i really need some expereinced suggestions from you all.

I have create Calender Table using Calender Function from 

Calender = CALENDAR(DATE(2018,1,1),TODAY())

Now i would like to create a calculate column which defines first three working days as positive workings days as 1,2,3 (Only excludes Sat & Sun), and in the same way month last three working days as negative working days -1,-2,-3 and Rest all dates are Blank.

The output that i am expecting is something as like this.

Can any one please help me with this.

Thanks,

Mohan

[amitchandak]

use in a column calculation startofmonth and endofmoth like given below. Do minor adjustment of +1 -1.

datediff(date,startofmonth(date),day)

datediff(endofmonth(date),(date),day)

Also change code use table[column] , where date has been used

Hope it helps.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Mohan221213]

Thanks for the reply @amitchandak .

I did tried as below by using your suggestions.

I guess i am half way through, but not completely.

First i did created a weekname column by excluding SAT and SUN 

WeekName =

VAR DayName = FORMAT(Calender[Date],"DDDD")

RETURN

SWITCH(TRUE(),

DayName= "Saturday" || DayName= "Sunday",BLANK(),DayName)

After this, i have written below DAX

=

VAR Days =

CALCULATE(COUNT(Calender[Date]),

FILTER(ALLEXCEPT(Calender,Calender[Month-Year]),

Calender[Date] <= EARLIER(Calender[Date]) &&Calender[WeekName] <> BLANK()))

VAR EndMonth = datediff(endofmonth(Calender[Date]),(Calender[Date]),DAY)-1

RETURN

SWITCH(TRUE(),

Days >=1 && Days <=3 && Calender[WeekName] <> BLANK(),Days,

EndMonth >=-3 && EndMonth <= -1, EndMonth,BLANK())

 Which is giving me Positive first three working days right result, but coming to the last three working days, it is not.

I did checked for Mar-2018 the result is showing as below.

Can you please suggest me how can i correct it.

Thanks,

Mohan

[amitchandak]

Can you share sample data in excel along with the expected results?  If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Mohan221213]

@amitchandak  there is no sample data or sensitive data for this.

i directly created this calender table and i am trying to create a column which can give me the result as i showed in my question.

[v-lionel-msft]

Hi @Mohan221213 ,

I have researched @amitchandak 's answer, which is very creative, but there seems to be a problem, I will continue to research the problem.

Best Regards,
Lionel Chen

[amitchandak]

@Mohan221213 

I create two column. You can merge those.

https://www.dropbox.com/s/bs6m71k27gc76vc/datediff.pbix?dl=0

Also need of a new column for start of month can aslo we removed.

Date diff start of Month = if('Date'[Week day] <6,COUNTx(FILTER('Date','Date'[Week day] <6 && 'Date'[Start of Month]= EARLIER('Date'[Start of Month]) && 'Date'[Date]<= EARLIER('Date'[Date])),'Date'[Date]),0)

Date diff End of Month = -1*if('Date'[Week day] <6,COUNTx(FILTER('Date','Date'[Week day] <6 && 'Date'[Start of Month]= EARLIER('Date'[Start of Month]) && 'Date'[Date]>= EARLIER('Date'[Date])),'Date'[Date]),0)

Hope this will help.