PREVIOUSMONTH with either IF or FILTERS

[Anonymous]

Hi, 

I am trying to build a measure based on the below table that reports the previous month's employee count but only for the client's first month as terminated status.  the highlighted column shows the results desired. 

if i use calculate(sum(employees),filter(status="Terminated") then i get 8 for Feb and 5 for March, but i would like to pull the numbers from the previous month instead so my totals for Feb=10 and Mar=25  i have tried the below formula, it doesnt error out but gives blanks. 

Terminated Employees = CALCULATE(SUM(Sheet1[Employees]),PREVIOUSMONTH(Sheet1[Date]),filter(Sheet1,Sheet1[Status]="Terminated"))

Date	Client#	Status	Employees	Terminated Date	Terminated Employees
1/31/2018	1	Active	10
2/28/2018	1	Terminated	8	2/28/2018	10
3/31/2018	1	Terminated		2/28/2018
4/30/2018	1	Terminated		2/28/2018
1/31/2018	2	Active	20
2/28/2018	2	Active	25
3/31/2018	2	Terminated	5	3/31/2018	25
4/30/2018	2	Terminated		3/31/2018

[amitchandak]

@Anonymous , you should date calendar for this

Terminated Employees = CALCULATE(sum(Sheet1[Employees]),PREVIOUSMONTH(Date[Date]),filter(Sheet1,Sheet1[Status]="Terminated"))

or

Terminated Employees = CALCULATE(count(Sheet1[Employees]),PREVIOUSMONTH(Date[Date]),filter(Sheet1,Sheet1[Status]="Terminated"))

The assumption is date is joined to the termination date. Else refer the example to use userelation

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[jeroendekker]

Hi,

Not sure what you are trying to show, however the reason you get a blank result is because the date reference in PREVIOUSMONTH is not a column that contains all possible dates.

I would create a datetable, create a relationship to date in your sheet. and use a function like this

Measure =
CALCULATE (
    SUM ( 'sheet'[Employees] ),
    PREVIOUSMONTH ( TblCalendar[Date] ),
    sheet[Status] = "Terminated"
)

How ever this will only show the 8 and 5 (sum of employee where status in terminated) but shifted a month.  Not sure if this is what you where expecting.

Best regards,

Jeroen 

[Anonymous]

@jeroendekker  

this is just a small sample of my entire table, so I'd rather not have to build out an entire table