cancel
Showing results for
Search instead for
Did you mean:
Highlighted
Frequent Visitor

## Need to know employee unit from previous month

Hi All,

Need you  suggestion to achieve this,

I have to know the employee unit from previous month and currrent month if both units are same then i do not have to count him under movement  if not same  i have count him under movement.

I have a date field which has only start date of the month.

Could any one please help me on completing this?

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Need to know employee unit from previous month

We can create two measure like following to count the employee movement depends on the selected month.

```NoMovement =
VAR selectedDate =
CALCULATE ( MAX ( 'Unit'[Date] ), ALLSELECTED ( Unit[Date] ) )
VAR thisMonth =
DATE ( YEAR ( selectedDate ), MONTH ( selectedDate ), 1 )
VAR lastMonth =
IF (
MONTH ( thisMonth ) = 1,
DATE ( YEAR ( selectedDate ) - 1, 12, 1 ),
DATE ( YEAR ( selectedDate ), MONTH ( selectedDate ) - 1, 1 )
)
VAR twoMonthUnit =
FILTER (
ALL ( Unit ),
AND ( Unit[Date] >= lastMonth, Unit[Date] <= selectedDate )
)
VAR resultUnit =
ADDCOLUMNS (
twoMonthUnit,
"monthcount", COUNTROWS ( FILTER ( twoMonthUnit, [ID] = EARLIER ( Unit[ID] ) ) )
)
VAR resultTable =
FILTER ( resultUnit, [monthcount] = 1 )
RETURN
INT ( ABS ( COUNTROWS ( resultUnit ) - COUNTROWS ( resultTable ) ) / 2 )
```
```UnderMovement =
VAR selectedDate =
CALCULATE ( MAX ( 'Unit'[Date] ), ALLSELECTED ( Unit[Date] ) )
VAR thisMonth =
DATE ( YEAR ( selectedDate ), MONTH ( selectedDate ), 1 )
VAR lastMonth =
IF (
MONTH ( thisMonth ) = 1,
DATE ( YEAR ( selectedDate ) - 1, 12, 1 ),
DATE ( YEAR ( selectedDate ), MONTH ( selectedDate ) - 1, 1 )
)
VAR twoMonthUnit =
FILTER (
ALL ( Unit ),
AND ( Unit[Date] >= lastMonth, Unit[Date] <= selectedDate )
)
VAR resultUnit =
ADDCOLUMNS (
twoMonthUnit,
"monthcount", COUNTROWS ( FILTER ( twoMonthUnit, [ID] = EARLIER ( Unit[ID] ) ) )
)
VAR resultTable =
FILTER ( resultUnit, [monthcount] = 1 )
RETURN
COUNTROWS ( resultTable )
```

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Community Support Team

## Re: Need to know employee unit from previous month

We can create two measure like following to count the employee movement depends on the selected month.

```NoMovement =
VAR selectedDate =
CALCULATE ( MAX ( 'Unit'[Date] ), ALLSELECTED ( Unit[Date] ) )
VAR thisMonth =
DATE ( YEAR ( selectedDate ), MONTH ( selectedDate ), 1 )
VAR lastMonth =
IF (
MONTH ( thisMonth ) = 1,
DATE ( YEAR ( selectedDate ) - 1, 12, 1 ),
DATE ( YEAR ( selectedDate ), MONTH ( selectedDate ) - 1, 1 )
)
VAR twoMonthUnit =
FILTER (
ALL ( Unit ),
AND ( Unit[Date] >= lastMonth, Unit[Date] <= selectedDate )
)
VAR resultUnit =
ADDCOLUMNS (
twoMonthUnit,
"monthcount", COUNTROWS ( FILTER ( twoMonthUnit, [ID] = EARLIER ( Unit[ID] ) ) )
)
VAR resultTable =
FILTER ( resultUnit, [monthcount] = 1 )
RETURN
INT ( ABS ( COUNTROWS ( resultUnit ) - COUNTROWS ( resultTable ) ) / 2 )
```
```UnderMovement =
VAR selectedDate =
CALCULATE ( MAX ( 'Unit'[Date] ), ALLSELECTED ( Unit[Date] ) )
VAR thisMonth =
DATE ( YEAR ( selectedDate ), MONTH ( selectedDate ), 1 )
VAR lastMonth =
IF (
MONTH ( thisMonth ) = 1,
DATE ( YEAR ( selectedDate ) - 1, 12, 1 ),
DATE ( YEAR ( selectedDate ), MONTH ( selectedDate ) - 1, 1 )
)
VAR twoMonthUnit =
FILTER (
ALL ( Unit ),
AND ( Unit[Date] >= lastMonth, Unit[Date] <= selectedDate )
)
VAR resultUnit =
ADDCOLUMNS (
twoMonthUnit,
"monthcount", COUNTROWS ( FILTER ( twoMonthUnit, [ID] = EARLIER ( Unit[ID] ) ) )
)
VAR resultTable =
FILTER ( resultUnit, [monthcount] = 1 )
RETURN
COUNTROWS ( resultTable )
```

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

## Helpful resources

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 39 members 1,049 guests
Recent signins:
Please welcome our newest community members: