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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
lamlamm24
Helper I
Helper I

weekly date breakdown.

lamlamm24_0-1680721451493.png

I am trying to achieve the above. Have a date range for instance 3/12/2023 - 3/18/2023 and when i expand the + sign on a matrix visual I would love to see the dates in between 3/13/2023, 3/14/2023,3/15/2023, 3/16/2023,3/17/2023,3/18/2023.

 

5 REPLIES 5
v-shex-msft
Community Support
Community Support

HI @lamlamm24,

You can try to create a calculated column with following formulas to show the current week ranges that include year start/end checkings:

Weekbreakdown =
VAR sWeek =
    [Date] - WEEKDAY ( [Date], 1 ) + 1
RETURN
    IF (
        WEEKNUM ( [Date], 1 ) <> 1
            && WEEKNUM ( [Date], 1 ) <> WEEKNUM ( DATE ( YEAR ( [Date] ), 12, 31 ), 1 ),
        sWeek & "-" & sWeek + 6,
        IF (
            WEEKNUM ( [Date], 1 ) = 1,
            DATE ( YEAR ( [Date] ), 1, 1 ) & "-" & sWeek + 6,
            sWeek & "-"
                & DATE ( YEAR ( [Date] ), 12, 31 )
        )
    )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
lamlamm24
Helper I
Helper I

This is the DAX i used for week break down but its Blank. 

Week Date Range =
VAR StartDate = MIN('Date'[Date])
VAR EndDate = TODAY()
VAR Weeks = WEEKNUM(EndDate) - WEEKNUM(StartDate) + 1
RETURN
GENERATESERIES(
    StartDate - WEEKDAY(StartDate, 2) + 1,
    StartDate -WEEKDAY(StartDate, 2) + (Weeks * 7),
    7
)

I also tried this DAX

Wek Breakdown =
    FORMAT(DATEADD('Date'[Date], -WEEKDAY('Date'[Date], 1), DAY), "MM/dd")
    & " - " &
    FORMAT(DATEADD('Date'[Date], 7 - WEEKDAY('Date'[Date], 1), DAY), "MM/dd"). 
This sorts of gives me what i want but it is not including today's date.
If i try to include this week 04/01 -today's date, it shows me 12/29
vicky_
Super User
Super User

If you have a date calendar, then you can just drag both fields over to a table and it should show the dates between. Depending on the type of visual you are using (e.g. matrix), there may be an option to show the +/- buttons under formatting.

vicky__3-1680732459004.png

 

This is exactly what i want to see. But what DAX did you use for the WeekDateRange. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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