Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mohan_g_das1986
Helper III
Helper III

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

 

 

 

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

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)
6.png

 

View solution in original post

1 REPLY 1
v-juanli-msft
Community Support
Community Support

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)
6.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.