Forum Discussion

Mohan221213's avatar
Mohan221213
New Member
6 years ago

Calculate positive and Negative WorkingDays

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

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Mohan221213
      New Member
      6 years ago

      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