Forum Discussion

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
    • 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.