Forum Discussion
dax help
Hi Truelearner ,
We can create a caluclated table and a measure to meet your requirement:
Calculated table:
Date = ADDCOLUMNS(CALENDARAUTO(),"Month",FORMAT([Date],"YYYY-MMM"),"Sort",VALUE(FORMAT([Date],"YYYYMM")))Measure:
Measure = CALCULATE(DISTINCTCOUNT('Table'[empid]),FILTER(ALLSELECTED('Table'),not('Table'[startdate]> Max('Date'[Date]) || 'Table'[enddate]< Min('Date'[Date]))))but we cannot understand "when people select Feb in month they should be shown total members assigned in project", because the EMP 1 in start from 18-03-2020 and end to 03-04-2020, it does not have work in Feb, Could you please share the logic why the total of Feb is 3?
By the way, PBIX file as attached.
Best regards,Hi Truelearner ,
If you 2019 June, we find the resource id 1 is start from 2019-4-1 and end at 2020-5-29, so it count as 1 in 2019-june and 2019-july.
If you have any other questions, please kindly ask here and we will try to resolve it.
Best regards,
ITS NOW WORKING WITH MY ACTUAL DATA , CAN YOU CHECK ONCE , AS PER THE DATA THERE IS NO ALLOCATION IN THE MONTHS OF JUN AND JULY BUT STILL IT IS GIVING 1
PLEASE DOWNLOAD THE FILE FROM https://drive.google.com/open?id=1O4_72OSpDPjyGhnSGgMYaoRz1LsJMnyX
Hi Truelearner ,
If you 2019 June, we find the resource id 1 is start from 2019-4-1 and end at 2020-5-29, so it count as 1 in 2019-june and 2019-july.
If you have any other questions, please kindly ask here and we will try to resolve it.
Best regards,
my bad 🙂 i misread it enddate as 2019 May