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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
RvdHeijden
Post Prodigy
Post Prodigy

Transform a date to year-month

I have a question and i need your help.

 

I have a column with dates in the following format 3-2-2017 07:49:00 and i want to transform that to a year-weeknumber for example 2017-05.

 

My formula can get the year and month correctly but then it reads 2017-5 and it leaves the extra 0 because i want it to read 2017-05

If i do not get the extra 0 my visual will place the values wrong.

 

Because you get:

2016-1 - 2016-10 - 2016-11 - 2016-2 - 2016-3

instead of:

2016-1 - 2016-2 - 2016-3 - 2016-10 - 2016-11

 

That is why i need the extra 0, so i can get:

2016-01 - 2016-02 - 2016-03 - 2016-10 - 2016-11

I hope you guys understand what i am trying to do

 

Binnenkomst Ticket = Tickets[Startdatum].[Year]&"-"&Tickets[Startdatum].[MonthNo]

2 ACCEPTED SOLUTIONS
Sean
Community Champion
Community Champion

@RvdHeijden

Try this instead

Year-Mon = FORMAT('Table'[Date], "YYYY-MM")

Good Luck! Smiley Happy

View solution in original post

Sean
Community Champion
Community Champion

@RvdHeijdenHere's DAX

 

Year-Wk = 
    YEAR ( 'Calendar'[Date] ) & "-"
        & CONCATENATE (
            IF ( WEEKNUM ( 'Calendar'[Date] ) < 10, "0", "" ),
            WEEKNUM ( 'Calendar'[Date] )
        )

 

Yes you may have to adjust WEEKNUM depending on when your week starts SUN (1) on MON (2)

I have skipped this optional parameter because the default is 1 which is what i need.

 

Hope this helps.

Good Luck!Smiley Happy

 

View solution in original post

14 REPLIES 14
MarcelBeug
Community Champion
Community Champion

Just in case you mean week number (as you mention in your example) instead of month number, and because I spot some Dutch in your coding, the code below is a M function (Power Query / Query Editor) that wll return the ISO Week Number, based on a date.

 

let
    Source = (DateParameter as date) => let
    Thursday = Date.AddDays(DateParameter,3-Date.DayOfWeek(DateParameter,Day.Monday)),
    Jan1 = #date(Date.Year(Thursday),1,1),
    Days = Number.From(Thursday - Jan1),
    Result = Number.RoundDown(Days/7)+1
in
    Result
in
    Source

 

This will only return the week number.

 

If you want year-week number, replace the "Result = ..."  line by:

 

    Result = Text.From(Date.Year(Thursday))&"-"&Text.PadStart(Text.From(Number.RoundDown(Days/7)+1),2,"0")
Specializing in Power Query Formula Language (M)

@MarcelBeug your right, i was to fast and however @Sean formula works it returns year-month instead of year-weeknumber

But i do not fully understand your formula Marcel.

 

Im a relative nooby as it comes to these types of formula. Can you give me the complete formula so i can copy/paste is in PowerBi ?

 

Well, the formula is actually incorporated in the funtion.

I'm not experienced with DAX, but I can provide the rules for ISO week number and the translation of the function steps in English.

 

Rules for ISO week number:
1. First day of the week is Monday.
2. Each week from Monday to Sunday has the same week number.
3. Week 1 of the year is the week with the first Thursday of the year,
meaning more than half of the days of the week are in week 1.
If January 1st is on Monday-Wednesday 0-2, it will be week 52 or 53 of the preceding year.
If January 1st is on Thursday-Sunday 3-6, it will be week 1 of the current year.

 

Steps to determine ISO week number:
1. Determine the date of Thursday of the current week.
2. Determine January 1st of the date from step 1.
3. Calculate the number of days from January 1st to the date from step 1.
4. Divide the number of days from step 3 by 7, round down and add 1.

Specializing in Power Query Formula Language (M)

@MarcelBeug i know the ISO formula in excel but DAX is different and im not experienced enough with DAX to make a formula based on your info sorry but i really appreciate the help


Does anyone know how to create the formula based on MarcelBeugs info ?

Sean
Community Champion
Community Champion

@RvdHeijdenHere's DAX

 

Year-Wk = 
    YEAR ( 'Calendar'[Date] ) & "-"
        & CONCATENATE (
            IF ( WEEKNUM ( 'Calendar'[Date] ) < 10, "0", "" ),
            WEEKNUM ( 'Calendar'[Date] )
        )

 

Yes you may have to adjust WEEKNUM depending on when your week starts SUN (1) on MON (2)

I have skipped this optional parameter because the default is 1 which is what i need.

 

Hope this helps.

Good Luck!Smiley Happy

 

@Sean

i want the week to start on a monday so where should i place this variable in the formula ?

 

Year-Wk = YEAR ( 'Calendar'[Date] ) & "-" & CONCATENATE ( IF ( WEEKNUM ( 'Calendar'[Date] ) < 10, "0", "" ), WEEKNUM ( 'Calendar'[Date] ) )

Refering to my previous post, I would guess

Year-Wk = YEAR ( 'Calendar'[Date] ) & "-" & CONCATENATE ( IF ( WEEKNUM ( 'Calendar'[Date],21 ) < 10, "0", "" ), WEEKNUM ( 'Calendar'[Date] , 21) )

 

But I'm not a DAX expert.

 

Otherwise, in ISO you can't just take the year from the date, e.g. Sunday 1/1/2017 is week 2016-52 and Monday 12/31/2018 is week 2019-01.

 

If you can just have my M-formula translated to DAX (or just use the M-solution), then you're good.

Specializing in Power Query Formula Language (M)

Wow....thanks again @Sean that did the trick Smiley Happy

Sorry, but I'm afraid not. Probably the DAX week number does NOT return the ISO Week number (unless I'm terribly mistaking as I'm not a DAX expert).

Specializing in Power Query Formula Language (M)

It seems that you can supply 21 as second parameter to the WEEKNUM Function to obtain the ISO Week Number, see comments below Microsofts documenetation.

Specializing in Power Query Formula Language (M)
Sean
Community Champion
Community Champion

@RvdHeijden

Try this instead

Year-Mon = FORMAT('Table'[Date], "YYYY-MM")

Good Luck! Smiley Happy

How is this supposed to be used in the context of the query? 

I tried adding this to a Table.AddColumn() clause as the last argument and it doesnt recognised FORMAT, What am I missing? 

 

 

Hi Sean,

 

I am trying to create a custom column to extract year/month from my date which is currently in the following format: 01/01/2017 but when I use the formula below, I recieve a 'Token Literal Expected' error for the 'Table' piece. 

 

=FORMAT('Table'[Date], "YYYY-MM")

 

Also, if I try and use basic functions such as CONCATENATE, FORMAT, MONTH, YEAR, etc, I get an error that Power BI does not recognize the function. Thanks for the advice!

 

 

@Sean

Your 2 for 2, thanks a bunch, this works

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.