Forum Discussion

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

    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.

     

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

      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.

    • v-lionel-msft's avatar
      v-lionel-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      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