current month weekly data

[rebam12]

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 

[bhanu_gautam]

@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]

got an eror Cannot find table 'DateTable'.

[bhanu_gautam]

@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

[rebam12]

@bhanu_gautam ?

[bhanu_gautam · Accepted solution]

@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

[rebam12]

i already mentioned.. i have a file .. excel file  which i import in power bi .. and column name is dispatch date.. i already shared the link of file . ..