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 , 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
      • rebam12's avatar
        rebam12
        Helper I
        1 year ago

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