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...
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

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
  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    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