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
kollasv
Helper I
Helper I

need a DAX measure to get all weeks submitted resource list in a month

Hi All,Thankyou for your valuable time, here is a list of the resource who submitted the timesheets accordingly,please find a measure which gives resources list who were submitted timesheet for all the weeks in a month,if you see A & D are the list of resource who were submitted all the weeks in july month, i need that output and one more thing that database is not capturing the data of a resource who have not submitted timesheet in that particular month/week.  here "Timeshet startdate" column is a date data type and it is from 2021 to till date,and updating everyday and it is a direct query mode,please find the exact format of my data in picture and  please find a measure please.

kollasv_0-1660660849880.png

 

 

1 REPLY 1
Greg_Deckler
Super User
Super User

@kollasv So something like:

Measure = 
  VAR __Start = DATE(2022,7,1)
  VAR __End = DATE(2022,7,31)
  VAR __Rows = FILTER('Table',[Timesheet start date]>=__Start && [Timesheet start date]<=__End)
  VAR __Weeks = COUNTROWS(DISTINCT(SELECTCOLUMNS(__Rows,"__TSD",[Timesheet start date])))
  VAR __Resources = 
    SUMMARIZE(__Rows,[Resource name],"__WeeksSubmitted", COUNTROWS(DISTINCT(SELECTCOLUMNS(__Rows,"__TSD",[Timesheet start date]))))
RETURN
  CONCATENATEX(FILTER(__Resources, [__WeeksSubmitted] = __Weeks),"[Resource name],",")

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

Top Solution Authors