Forum Discussion

mohan_g_das1986's avatar
mohan_g_das1986
Icon for Helper III rankHelper III
7 years ago
Solved

VERY URGENT!! - Show all Completed Week data

Hi, I need an very urgent request to show the units for only completed weeks; below is the week structure

 

  1. When Today is July 10, 2019 - Last Completed Week is - July 01, 2019 (Monday) to July 7, 2019 (Sunday)
  2. When Today is July 20, 2019 - Last Completed Week is - July 08, 2019 (Monday) to July 14, 2019 (Sunday)

Below is the link for my previous question still no answer;

https://community.powerbi.com/t5/Desktop/DAX-to-show-all-N-completed-weeks-data/m-p/745014#M359143

i tried Weekoffset & it works for last 2 weeks and not for all completed weeks (maybe 13 or 15 weeks)

Used another DAX measure 

Flag_Test = If(format([Last Refreshed], "mm dd yyyy") = format(Calendar_time[Date], "mm dd yyyy"),"Current",

            If((([Last Refreshed]- Calendar_time[Date])-1) < 0,"Future","Past"))
 
Eventhough my flag is past still incomplete w

 

 

 

Need Help
Tips and Tricks
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi mohan_g_das1986 

    Create new tables

    calendar =
ADDCOLUMNS (
    CALENDARAUTO (),
    "year", YEAR ( [date] ),
    "month", MONTH ( [date] ),
    "weeknum", WEEKNUM ( [date], 2 ),
    "year-weeknum", YEAR ( [Date] ) & WEEKNUM ( [Date], 2 )
)
    weekset = VALUES('calendar'[weeknum])

    Don't create any relationship for "weekset" table

     

    Create measures in Sheet3

    selected_weeknumber = SELECTEDVALUE(weekset[weeknum])

current_week = WEEKNUM(TODAY(),2)

last week = [current_week]-[selected_weeknumber]

flag = IF(MAX('calendar'[weeknum])<[current_week]&&MAX('calendar'[weeknum])>=[last week],1,0)

     

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi mohan_g_das1986 

    Create new tables

    calendar =
ADDCOLUMNS (
    CALENDARAUTO (),
    "year", YEAR ( [date] ),
    "month", MONTH ( [date] ),
    "weeknum", WEEKNUM ( [date], 2 ),
    "year-weeknum", YEAR ( [Date] ) & WEEKNUM ( [Date], 2 )
)
    weekset = VALUES('calendar'[weeknum])

    Don't create any relationship for "weekset" table

     

    Create measures in Sheet3

    selected_weeknumber = SELECTEDVALUE(weekset[weeknum])

current_week = WEEKNUM(TODAY(),2)

last week = [current_week]-[selected_weeknumber]

flag = IF(MAX('calendar'[weeknum])<[current_week]&&MAX('calendar'[weeknum])>=[last week],1,0)