Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
vengadesh_p
Helper I
Helper I

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

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 

vengadesh_p_2-1679212000041.png

 

vengadesh_p_0-1679211696322.png

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)

vengadesh_p_3-1679212229713.png

 

vengadesh_p_1-1679211714983.png
How can i achieve this logic, Pls help me to resolve this Issue 

 

 

1 ACCEPTED SOLUTION
vengadesh_p
Helper I
Helper I

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))))

 

View solution in original post

3 REPLIES 3
vengadesh_p
Helper I
Helper I

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
Helper I
Helper I

@bhelou thanks for the reply 

 

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


bhelou
Responsive Resident
Responsive Resident

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors