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
LZNPM
Frequent Visitor

Switch from WEEKNUM to time range

Hello dear community,

I would like to create a new column on my Calendar Table who gives the time range instead of the week num. For example :

For Week number 1 of 2023, ill get a value like "02/01 to 08/01".

ChatGPT tried to give me this solution, who only works for the first year 2023 : 

Range =
VAR FirstDateOfWeek =
    MINX(
        FILTER(
            Calendrier,
            Calendrier[Semaine] = EARLIER(Calendrier[Semaine])
        ),
        Calendrier[Date]
    )
VAR LastDateOfWeek =
    MAXX(
        FILTER(
            Calendrier,
            Calendrier[Semaine] = EARLIER(Calendrier[Semaine])
        ),
        Calendrier[Date]
    )
VAR LastDateOfWeekMinusOne =
    LastDateOfWeek - 1
VAR LastDateOfYear =
    CALCULATE(
        MAX(Calendrier[Date]),
        CALENDRIER[Annee] = MAX(Calendrier[Annee])
    )
RETURN
    "Du " & FORMAT(FirstDateOfWeek, "dd/MM") & " au " &
    IF(
        LastDateOfWeek = LastDateOfYear,
        FORMAT(LastDateOfWeek, "dd/MM"),
        FORMAT(LastDateOfWeekMinusOne, "dd/MM")
    )
 
Does someone here know an elegant way to provide this Range column for every year on a very large calendar ?
 
A great thank you for your reading,
 
LZ
2 REPLIES 2
Greg_Deckler
Super User
Super User

@LZNPM Try:

Range = 
    VAR __WeekNum = [WeekNum]
    VAR __Year = YEAR([Date])
    VAR __Min = MINX(FILTER('Dates', [WeekNum] = __WeekNum && YEAR([Date]) = __Year), [Date])
    VAR __Max = MAXX(FILTER('Dates', [WeekNum] = __WeekNum && YEAR([Date]) = __Year), [Date])
    VAR __Return = FORMAT(__Min, "dd/MM") & " to " & FORMAT(__Max, "dd/MM")
RETURN
    __Return

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Thank you very much Greg ! Your code works very well... until the year switch ! For all weeks in 2023 the correct time range is written, but for the first week of 2024, the value of the new column is "01/01 to 31/12". How can I correct it ? If you have an idea.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors