Forum Discussion

eryka_90's avatar
eryka_90
Icon for Helper I rankHelper I
2 years ago
Solved

How to calculate Recent Week and Past Week

Hello All,

 

I'm trying to create new column to get information on Recent Week and Past Week. Here is DAX use:

 

1. Check Current or Prior Wk = If( 'Pending Items'[Load Week]= MAXX(FILTER('Pending Items','Pending Items'[New_Adjusted_LoadDateWeek] = MAX('Pending Items'[New_Adjusted_LoadDateWeek])),'Pending Items'[Load Week]), "Recent Week", ( If( 'Pending Items'[Load Week]= MAXX(FILTER('Pending Items','Pending Items'[Load Week]<>MAXX(FILTER('Pending Items','Pending Items'[New_Adjusted_LoadDateWeek] = MAX('Pending Items'[New_Adjusted_LoadDateWeek])),'Pending Items'[Load Week])),'Pending Items'[Load Week]), "Past Week","(Ensure Untick) History Week" ) ) )

This formula giving correct value for Recent Week but wrong value for Past Week

 

2. New_Current and Prior Load Week =
VAR CurrentYear = YEAR(TODAY())
VAR MaxLoadWeek = MAX('Pending Items'[Load Week])
VAR MaxLoadDate = MAX('Pending Items'[Load Date])
RETURN
    IF (
        YEAR(MaxLoadDate) = CurrentYear && 'Pending Items'[Load Week] = MaxLoadWeek,
        "Current Week",
        IF (
            YEAR(MaxLoadDate) = CurrentYear && 'Pending Items'[Load Week] = MaxLoadWeek - 1,
            "Past Week",
            BLANK()
        )
    )
This formula showing max load week instead of max load date
 
Here is sample data:
Load DateLoad WeekCurrent and Prior Load WeekNumber of IDExpected Result
1/20/20231 16096 
1/17/20241 6003 
1/20/20232 16072 
1/17/20242 6141 
1/21/20233 15485 
1/20/20243 5746 
1/28/20234 15478 
1/28/20244 5840 
2/5/20235 15751 
1/31/20245 6132Past Week
2/3/20245 5887 
2/11/20236 15764 
2/5/20246 6180Current Week
2/18/20237 15559 
2/25/20238 15701 
3/4/20239 17710 
3/18/202310Past Week15146 
3/18/202311Current Week18249 
Need Help
Tutorial Requests
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

    It is for creating a new column.

     

     

     

    expected result CC =
VAR _calendartable =
    ADDCOLUMNS (
        CALENDAR (
            DATE ( YEAR ( MIN ( Data[Load Date] ) ), 1, 1 ),
            DATE ( YEAR ( MAX ( Data[Load Date] ) ), 12, 31 )
        ),
        "Year", YEAR ( [Date] ),
        "Weeknumber", WEEKNUM ( [Date], 21 )
    )
VAR _currentweekyear =
    MAXX ( FILTER ( _calendartable, [Date] = TODAY () ), [Year] )
VAR _currentweekweeknumber =
    MAXX ( FILTER ( _calendartable, [Date] = TODAY () ), [Weeknumber] )
VAR _prevweekyear =
    MAXX ( FILTER ( _calendartable, [Date] = TODAY () - 7 ), [Year] )
VAR _prevweekweeknumber =
    MAXX ( FILTER ( _calendartable, [Date] = TODAY () - 7 ), [Weeknumber] )
VAR _currentweeklist =
    SUMMARIZE (
        FILTER (
            _calendartable,
            [Year] = _currentweekyear
                && [Weeknumber] = _currentweekweeknumber
        ),
        [Date]
    )
VAR _prevweeklist =
    SUMMARIZE (
        FILTER (
            _calendartable,
            [Year] = _prevweekyear
                && [Weeknumber] = _prevweekweeknumber
        ),
        [Date]
    )
RETURN
    SWITCH (
        TRUE (),
        Data[Load Date] IN _currentweeklist, "Current Week",
        Data[Load Date] IN _prevweeklist, "Previous Week"
    )

     

3 Replies

  • sayaliredij's avatar
    sayaliredij
    Icon for Solution Sage rankSolution Sage
    2 years ago

    HI eryka_90 

     

    For the First columm ,which is just related to current week and past week i assume ,

    I tried following measure. 

    Check Current or Prior Wk =

    var currentweek = WEEKNUM(TODAY())

    var currentYear = Year(TODAY())

    var currentdate = TODAY()

    var pastweek = WEEKNUM(TODAY() - 7)

    var pastweekyear = YEAR(TODAY() - 7)

    RETURN

    IF(AND(currentweek = WEEKNUM('Table'[Load Date]),currentYear = YEAR('Table'[Load Date])),"Current Week",

    IF(AND(pastweek = WEEKNUM('Table'[Load Date]),pastweekyear = YEAR('Table'[Load Date])),"Past Week",BLANK()))

    although this data has more than one entries from the past date . so could you clarify that little bit

     

    for second measure

    Try following measure

    New_Current and Prior Load Week =

    var maxload_week = MAXX(FILTER('Table','Table'[Number of ID] = MAX('Table'[Number of ID])),'Table'[Load Week])

    RETURN IF('Table'[Load Week] = maxload_week, "Max Load week", blank())

    It would return you max load week and the you can adjust it for past week as well


    Please check if this helps 

    Thanks and Regards,

     

    Sayali

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful!! 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

    It is for creating a new column.

     

     

     

    expected result CC =
VAR _calendartable =
    ADDCOLUMNS (
        CALENDAR (
            DATE ( YEAR ( MIN ( Data[Load Date] ) ), 1, 1 ),
            DATE ( YEAR ( MAX ( Data[Load Date] ) ), 12, 31 )
        ),
        "Year", YEAR ( [Date] ),
        "Weeknumber", WEEKNUM ( [Date], 21 )
    )
VAR _currentweekyear =
    MAXX ( FILTER ( _calendartable, [Date] = TODAY () ), [Year] )
VAR _currentweekweeknumber =
    MAXX ( FILTER ( _calendartable, [Date] = TODAY () ), [Weeknumber] )
VAR _prevweekyear =
    MAXX ( FILTER ( _calendartable, [Date] = TODAY () - 7 ), [Year] )
VAR _prevweekweeknumber =
    MAXX ( FILTER ( _calendartable, [Date] = TODAY () - 7 ), [Weeknumber] )
VAR _currentweeklist =
    SUMMARIZE (
        FILTER (
            _calendartable,
            [Year] = _currentweekyear
                && [Weeknumber] = _currentweekweeknumber
        ),
        [Date]
    )
VAR _prevweeklist =
    SUMMARIZE (
        FILTER (
            _calendartable,
            [Year] = _prevweekyear
                && [Weeknumber] = _prevweekweeknumber
        ),
        [Date]
    )
RETURN
    SWITCH (
        TRUE (),
        Data[Load Date] IN _currentweeklist, "Current Week",
        Data[Load Date] IN _prevweeklist, "Previous Week"
    )

     

    • eryka_90's avatar
      eryka_90
      Icon for Helper I rankHelper I
      2 years ago

      Hi Jihwan_Kim,

       

      Your solution give the results as expected. Eventhough I need to do some filtering to have only one previous week.

       

      Thank you for helping 😃