Forum Discussion

rebam12's avatar
rebam12
Helper I
1 year ago
Solved

current month weekly data

hi 

 

i am trying to get current month weekly data .. i tried this formula but still i did not get it current month weekly data .. \

 

 

Week_Ranges3 = 
VAR CurrentDate = 'Export'[Dispatch Date (End)]
VAR CurrentMonthStart = DATE(YEAR(CurrentDate), MONTH(CurrentDate), 1) -- First day of the current month
VAR CurrentMonthEnd = EOMONTH(CurrentDate, 0) -- Last day of the current month
VAR WeekStart = CurrentDate - WEEKDAY(CurrentDate, 2) + 1 -- Week starts on Monday
VAR WeekEnd = WeekStart + 6 -- Week ends on Sunday
VAR AdjustedWeekStart = MAX(WeekStart, CurrentMonthStart) -- Clamp to the month's start
VAR AdjustedWeekEnd = MIN(WeekEnd, CurrentMonthEnd) -- Clamp to the month's end
RETURN
    IF(
        AdjustedWeekStart <= CurrentMonthEnd && AdjustedWeekEnd >= CurrentMonthStart, 
        CONCATENATE(
            CONCATENATE(YEAR(AdjustedWeekStart), "/" & MONTH(AdjustedWeekStart) & "/" & DAY(AdjustedWeekStart)),
            " - " & YEAR(AdjustedWeekEnd) & "/" & MONTH(AdjustedWeekEnd) & "/" & DAY(AdjustedWeekEnd)
        ),
        BLANK()
    )

 

this is the data with dates

 

https://filetransfer.io/data-package/t8GhECJ4#link

where is the error 

Data Protection
Need Help
Show and Tell
Tips and Tricks
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    rebam12 Since you have an Excel file with a column named "Dispatch Date" and you want to calculate the weekly ranges for the current month, you can use the following DAX formula to create a calculated column in Power BI:

     

    Week_Ranges3 =
    VAR CurrentDate = TODAY()
    VAR CurrentMonthStart = DATE(YEAR(CurrentDate), MONTH(CurrentDate), 1) -- First day of the current month
    VAR CurrentMonthEnd = EOMONTH(CurrentDate, 0) -- Last day of the current month
    VAR WeekStart = CurrentMonthStart + (7 - WEEKDAY(CurrentMonthStart, 2)) % 7 -- First Monday of the current month
    VAR WeekEnd = WeekStart + 6 -- First Sunday of the current month
    VAR AdjustedWeekStart = MAX(WeekStart, 'Export'[Dispatch Date (End)] - WEEKDAY('Export'[Dispatch Date (End)], 2) + 1)
    VAR AdjustedWeekEnd = MIN(AdjustedWeekStart + 6, CurrentMonthEnd)
    RETURN
    IF(
    'Export'[Dispatch Date (End)] >= CurrentMonthStart && 'Export'[Dispatch Date (End)] <= CurrentMonthEnd,
    CONCATENATE(
    CONCATENATE(YEAR(AdjustedWeekStart), "/" & MONTH(AdjustedWeekStart) & "/" & DAY(AdjustedWeekStart)),
    " - " & YEAR(AdjustedWeekEnd) & "/" & MONTH(AdjustedWeekEnd) & "/" & DAY(AdjustedWeekEnd)
    ),
    BLANK()
    )

     

    But still ideal way is to use date table

6 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    rebam12 , Try using 

     

    Week_Ranges3 =
    VAR CurrentDate = TODAY() -- Use TODAY() to get the current date
    VAR CurrentMonthStart = DATE(YEAR(CurrentDate), MONTH(CurrentDate), 1) -- First day of the current month
    VAR CurrentMonthEnd = EOMONTH(CurrentDate, 0) -- Last day of the current month
    VAR WeekStart = CurrentMonthStart -- Start from the first day of the current month
    VAR WeekEnd = WeekStart + 6 -- Week ends on the 7th day from the start
    VAR Result =
    ADDCOLUMNS(
    GENERATESERIES(0, DATEDIFF(CurrentMonthStart, CurrentMonthEnd, DAY) / 7, 1),
    "WeekStart", WeekStart + [Value] * 7,
    "WeekEnd", MIN(WeekStart + [Value] * 7 + 6, CurrentMonthEnd)
    )
    RETURN
    CONCATENATEX(
    Result,
    CONCATENATE(
    CONCATENATE(YEAR([WeekStart]), "/" & MONTH([WeekStart]) & "/" & DAY([WeekStart])),
    " - " & YEAR([WeekEnd]) & "/" & MONTH([WeekEnd]) & "/" & DAY([WeekEnd])
    ),
    ", "
    )

    • rebam12's avatar
      rebam12
      Helper I
      1 year ago

      got an eror Cannot find table 'DateTable'.

      • bhanu_gautam's avatar
        bhanu_gautam
        Super User
        1 year ago

        rebam12 , It looks like you do not have Date Table created go to modelling tab and create a new date table using below DAX

        DateTable =
        ADDCOLUMNS (
            CALENDAR (DATE(2023, 1, 1), DATE(2023, 12, 31)),
            "Year", YEAR([Date]),
            "Month", MONTH([Date]),
            "Day", DAY([Date]),
            "MonthName", FORMAT([Date], "MMMM"),
            "Quarter", "Q" & FORMAT([Date], "Q")
        )
         
        And than create a relationship with your original table