dax help

[Truelearner]

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 

[v-lid-msft · Accepted solution]

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,

[v-lid-msft · Accepted solution]

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,

[Truelearner]

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 

[v-lid-msft · Accepted solution]

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,

[Truelearner]

my bad 🙂 i misread it enddate as 2019 May 

[amitchandak]

@Truelearner, Refer, if this can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Truelearner]

i went through it but i am not able to match my current requirement in the link provided.