Need help to create ISO WEEK, ISO Month, ISO YEAR Logic

[vengadesh_p]

Hello Everyone

I need help to create dax for ISO Month 

I have created dax for Normal Week | Month | Year 

,"Week No",WEEKNUM([Date],2)
,"Month",MONTH([Date])
,"Year",YEAR([Date])

also i created dax for ISO Week | Year

,"Iso Week",WEEKNUM([Date],21)
,"Iso Year",IF(AND(WEEKNUM([Date],21)<5,WEEKNUM([Date],2)>50),YEAR([Date])+1,
               IF(AND(WEEKNUM([Date],21)>50,WEEKNUM([Date],2)<5),YEAR([Date])-1,
                        YEAR([Date])
                    )
                )

but i need help to create dax for ISO Month 

Ex1: in this senario, 4 days come from 2020 DEC & 3 days come from 2021 JAN

      so for these 7 days  ISO Year Should be 2020 & ISO Month Should be DEC 

Ex2: in this senario, 5 days come from 2021 JAN & 2 days come from 2021 FEB

      so for these 7 days  ISO Year Should be 2021 & ISO Month Should be JAN (because most of the days come from JAN)

How can i achieve this logic, Pls help me to resolve this Issue 

 

[vengadesh_p · Accepted solution]

based on week start date i created ISO Month & ISO Year

,"Week No",WEEKNUM([Date],2)      
    ,"Week Start Date",[Date]-WEEKDAY([Date],2)+1
    ,"Week End Date",[Date]+7-WEEKDAY([Date],2)
    ,"Year Week Number", COMBINEVALUES("-",YEAR([Date]),FORMAT( WEEKNUM([Date]),"00"))

    ,"ISO Week",WEEKNUM([Date],21)    
    ,"ISO Month", Month ([Date] - WEEKDAY([Date],2) + 1)
    ,"ISO Year", Year ([Date] - WEEKDAY([Date],2) + 1)
    ,"ISO Year Week Number",  COMBINEVALUES("-", Year ([Date] - WEEKDAY([Date],2) + 1),FORMAT( WEEKNUM([Date],21),"00"))
    ,"ISO Quarter", if (Month ([Date] - WEEKDAY([Date],2) + 1) <=3, 1,
                        if (Month ([Date] - WEEKDAY([Date],2) + 1) <=6 , 2,
                            if (Month ([Date] - WEEKDAY([Date],2) + 1) <=9 , 3,
                                if (Month ([Date] - WEEKDAY([Date],2) + 1) <=12 , 4))))

[vengadesh_p]

@bhelou thanks for the reply 

for me its not working can you pls share your workout pbix file 

[bhelou]

Dear ,  i got help with it also , 

check this dax if it applies with you : 

Iso Month = 
VAR isoWeekNum = WEEKNUM(MAX('Table'[Date]),21)
VAR firstDayOfWeek = MAX('Table'[Date]) - WEEKDAY(MAX('Table'[Date]),2) + 1
VAR daysInFirstWeek = IF(WEEKDAY(firstDayOfWeek,2) <= 4, 7 - WEEKDAY(firstDayOfWeek,2) + 1, WEEKDAY(firstDayOfWeek,2) - 4)
VAR isoYear = IF(AND(isoWeekNum < 5, MONTH(firstDayOfWeek + (daysInFirstWeek-1)) = 12), YEAR(firstDayOfWeek + (daysInFirstWeek-1)) - 1,
                 IF(AND(isoWeekNum > 50, MONTH(firstDayOfWeek + (daysInFirstWeek-1)) = 1), YEAR(firstDayOfWeek + (daysInFirstWeek-1)) + 1,
                    YEAR(firstDayOfWeek + (daysInFirstWeek-1))
                 )
              )
VAR isoMonth = IF(MONTH(firstDayOfWeek) = 12 && MONTH(firstDayOfWeek + (daysInFirstWeek-1)) = 1 && isoWeekNum > 50, 1,
                  IF(MONTH(firstDayOfWeek) = 1 && MONTH(firstDayOfWeek + (daysInFirstWeek-1)) = 12 && isoWeekNum < 5, 12,
                     IF(isoWeekNum < 5 && MONTH(firstDayOfWeek + (daysInFirstWeek-1)) = 12, 12,
                        IF(isoWeekNum > 50 && MONTH(firstDayOfWeek + (daysInFirstWeek-1)) = 1, 1,
                           MONTH(firstDayOfWeek + (daysInFirstWeek-1))
                        )
                     )
                  )
               )
RETURN
isoMonth