Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

weekly date breakdown.

 

 

Need Help

5 Replies

  • vicky_'s avatar
    vicky_
    Super User
    3 years ago

    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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    HI Anonymous,

    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