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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
HarryB
Resolver I
Resolver I

Week Starting on Friday and ending on Thursday

Hi all,

 

I want to change my calendar so for some specific weekly calculations my week will start on Friday and end of Thursday.

This is my calendar below.

Which parts will I need to change below?

 

 

VAR __RESULT =
    ADDCOLUMNS (
        ADDCOLUMNS (
            __BASE,
            "Year", YEAR ( [Date] ),
            "Month Long", FORMAT ( [Date], "mmmm" ),
            "Month Short", FORMAT ( [Date], "mmm" ),
            "Month Number", MONTH ( [Date] ),
            "Month and Year", FORMAT ( [Date], "mmm-yy" ),
            "YYYYMM", FORMAT ( [Date], "MMM" ),
            "Quarter", "Q" & QUARTER ( [Date] ),
            "Day of Week Long", FORMAT ( [Date], "dddd" ),
            "Day of Week Short", FORMAT ( [Date], "ddd" ),
            "Day Sort", WEEKDAY ( [Date], 1 ),
            "Week of Year", WEEKNUM ( [Date] ),
            "Week Ending Saturday",
                VAR __DAY =
                    WEEKDAY ( [Date] ) - 6
                RETURN
                    [Date] - __DAY,
            "Week Ending Thursday",
                VAR __DAY =
                    WEEKDAY ( [Date] ) - 5
                RETURN
                    [Date] - __DAY
        ),
        "Week Starting Thursday", [Week Ending Thursday] - 6
    )
RETURN
    __RESULT
1 ACCEPTED SOLUTION
Sahir_Maharaj
Super User
Super User

Hello @HarryB,

 

Can you please try the following approach:

VAR __RESULT =
    ADDCOLUMNS (
        ADDCOLUMNS (
            __BASE,
            "Year", YEAR ( [Date] ),
            "Month Long", FORMAT ( [Date], "mmmm" ),
            "Month Short", FORMAT ( [Date], "mmm" ),
            "Month Number", MONTH ( [Date] ),
            "Month and Year", FORMAT ( [Date], "mmm-yy" ),
            "YYYYMM", FORMAT ( [Date], "MMM" ),
            "Quarter", "Q" & QUARTER ( [Date] ),
            "Day of Week Long", FORMAT ( [Date], "dddd" ),
            "Day of Week Short", FORMAT ( [Date], "ddd" ),
            "Day Sort", WEEKDAY ( [Date], 1 ),
            "Week of Year", WEEKNUM ( [Date], 2 ),
            "Week Ending Thursday",
                VAR __DAY =
                    WEEKDAY ( [Date], 2 ) - 7
                RETURN
                    [Date] - __DAY,
            "Week Starting Friday", 
                VAR __DAY = 
                    WEEKDAY ( [Date], 2 )
                RETURN
                    [Date] - __DAY + (7 - __DAY) % 7
        ),
        "Week Starting Thursday", [Week Ending Thursday] - 6
    )
RETURN
    __RESULT

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

View solution in original post

2 REPLIES 2
Sahir_Maharaj
Super User
Super User

Hello @HarryB,

 

Can you please try the following approach:

VAR __RESULT =
    ADDCOLUMNS (
        ADDCOLUMNS (
            __BASE,
            "Year", YEAR ( [Date] ),
            "Month Long", FORMAT ( [Date], "mmmm" ),
            "Month Short", FORMAT ( [Date], "mmm" ),
            "Month Number", MONTH ( [Date] ),
            "Month and Year", FORMAT ( [Date], "mmm-yy" ),
            "YYYYMM", FORMAT ( [Date], "MMM" ),
            "Quarter", "Q" & QUARTER ( [Date] ),
            "Day of Week Long", FORMAT ( [Date], "dddd" ),
            "Day of Week Short", FORMAT ( [Date], "ddd" ),
            "Day Sort", WEEKDAY ( [Date], 1 ),
            "Week of Year", WEEKNUM ( [Date], 2 ),
            "Week Ending Thursday",
                VAR __DAY =
                    WEEKDAY ( [Date], 2 ) - 7
                RETURN
                    [Date] - __DAY,
            "Week Starting Friday", 
                VAR __DAY = 
                    WEEKDAY ( [Date], 2 )
                RETURN
                    [Date] - __DAY + (7 - __DAY) % 7
        ),
        "Week Starting Thursday", [Week Ending Thursday] - 6
    )
RETURN
    __RESULT

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

@Sahir_Maharaj thank you for this. I appreciate your help

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.