ytd and empty date

[hmo]

Hello,

I have a list of accounts with opening and closing dates. Now I need a report that shows the number of open accounts over the month (Year to date, Month to date). The accounts are counted up to the month in which they are closed. I've tried with a calendar table and witgout a relationship to the accounttable, this doesn't work.

 

[amitchandak]

@hmo , Join both of them with date table one will active another will be inactive assume the closed_on is inactive

Try measures like with date tbale and time intelligence

YTD Open = CALCULATE(Count(Table[Account]),DATESYTD('Date'[Date],"12/31")) // date creation is active join

YTD closed = CALCULATE(CALCULATE(Count(Table[Account]), userelationship('Date'[Date], Table[Closed_on]), not(isblank(Table[Closed_on]))),DATESYTD('Date'[Date],"12/31"))

Refer why Time intellignce fails : https://youtu.be/OBf0rjpp5Hw

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 :radacad sqlbi My Video Series Appreciate your Kudos.

[hmo]

@amitchandak I think this is not the solution table below should end in chart below. We need to count all open accounts by month in the given time period.

Account;date_of_creation;closed_on
1;2019-12-10;2019-12-11
2;2019-12-10;2020-01-02
3;2019-12-10;
4;2019-12-10;2020-02-25
5;2019-12-10;2020-05-13
6;2020-01-03;2020-03-14
7;2020-03-11;2020-05-11

 

[amitchandak · Accepted solution]

@hmo , refer to my HR blog for same. Seem like similar problem

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

Check - https://youtu.be/e6Y-l_JtCq4

[hmo]

@amitchandak Thank you very much. I think this formula in your post leads me to the right results:

Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))