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