Forum Discussion
dax help
I have data in the above form where in which i get the number of hours worked for each employyed for an assignment period ( between Startdate and enddate ) and if the current date falls between the startdate and enddate the status flag will be active ifnot it is false.
I created month column from enddate , when people select Feb in month they should be shown total members assigned in project . In the above data example eveyone is assigned to project in Feb so the total count assigned has to be 3 , when user select month March from the date column created they should get total count as 2 becasue the assignment of EMP 2 is no more valid as its end date is 28-02-2020.
@mgwena @cham @amitchandak @Greg_Deckler @Mariusz
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,
6 Replies
i went through it but i am not able to match my current requirement in the link provided.
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,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,
