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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ashaikh
Helper III
Helper III

Start and End Date of a week

Hello,

 

I am creating weely reports for which I Week Duration which is calculated by Start Date and End Date of the week. This the formula I am using

 

 

WEEK DURATION = vRCCLCOMBINED[START DATE] & " - " & vRCCLCOMBINE[END DATE]

START DATE:

 

START DATE =
FORMAT (
    (
        vRCCLCOMBINED[START TIME] - WEEKDAY ( vRCCLCOMBINED[START TIME], 1 )
            + 1
    ),
    "MM/DD/YYYY"
)

END DATE:

 

 

END DATE =
FORMAT (
    (
        vRCCLCOMBINED[START TIME] - WEEKDAY ( vRCCLCOMBINED[START TIME], 1 )
            + 7
    ),
    "MM/DD/YYYY"
)

 

Now here is not the issue, but where I want to make changes is in case when Start of the year is with in the week.

 

Say for example, 01/01/2018 it starts on Monday so I want the START DATE 01/01/2018 and END DATE 01/06/2018. But for following week START DATE  01/07/2018 and END DATE 01/13/2018

 

Similary for last week of year 2017 should have START DATE 12/31/2018 and END DATE 12/31/2018

 

So the Output will look like below:

 

START DATE | END DATE  | WEEK DURATION          |
-----------|-----------|------------------------|
12/24/2017 |12/30/2017 |12/24/2017 - 12/30/2017 |
12/31/2017 |12/31/2017 |12/31/2017 - 12/31/2017 |
01/01/2018 |01/06/2018 |01/01/2018 - 01/06/2018 |
01/07/2018 |01/13/2018 |01/07/2018 - 01/13/2018 |

 

Thanks

Abdulquadir

 

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

You should be able to easily modify the quick measures that I have here for this:

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/First-Last-Working-Days/m-p/391545


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Vikram1
Frequent Visitor

Week Start & End Date From Date Dax Formula

 

WeekStartDate = [DateColumn] - (WEEKDAY([DateColumn])-1)

WeekEndDate = [DateColumn] + (7-WEEKDAY([DateColumn]))

Greg_Deckler
Super User
Super User

You should be able to easily modify the quick measures that I have here for this:

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/First-Last-Working-Days/m-p/391545


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Did you get this sorted out?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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