Dax Help with counting how many Start and End each day

[Anonymous]

Hello, 

I am stuck writing a DAX formula for about two days. I have a data table below with two dates per each row indicating start day and an end date. I would like to count the number of start day per month, and the count of end day per month. I am trying to calculate the net value of start date- end date per month. For example, I would want to know in January there are 8 start dates, and 2 end dates, I would want to get a value of +6. 

I am trying to get this value per month so I can create a waterfall chart. 

Thank you for your help!

[v-jingzhang]

Hi @Anonymous 

This blog should be helpful: How To Work With Multiple Dates In Power BI | Enterprise DNA

Make both relationships one (Date table) to many (Agency table) with single cross-filter direction. And try removing not(ISBLANK('Agency (2)'[EndDate])) from the measure. Put Month Year column from Date table into the table visual. And is your Date table wide enough to cover the End date 3/4/2022?

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[amitchandak]

@Anonymous , You can create a common date table and join it both start date and end date. You need to have a month year in the date table. then you can use userelationship in measure and use month from date table

USERELATIONSHIP is optional for active join

Started = CALCULATE(COUNTrows(Table]),USERELATIONSHIP(Table[Start Date],'Date'[Date]) )

Ended = CALCULATE(COUNTrows(Table]),USERELATIONSHIP(Table[End Date],'Date'[Date]),not(ISBLANK(Table[End Date])))

Refer this blog for more

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

if this does not help follow the suggestion by @VahidDM  and share the data.

[Anonymous]

I did follow your blog post and I coudn't get this work. I created a date table, and I did connect both the start date and end date with the common date table. 

I am trying to calculate the count of terminated employees with following measure:

Terminated Employees = CALCULATE(COUNT('Agency (2)'[Id]),USERELATIONSHIP('Agency (2)'[EndDate],'Date'[Date]),not(ISBLANK('Agency (2)'[EndDate]))). I am hoping to filter this out by Month-Yr column I created in the common date table. However, I cannot get the correct month of the end date to match with Month-year column.

[VahidDM]

Hi @Anonymous 

Does your report have a Date table?

Can you post sample data as text ?

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

[Anonymous]

Yes I do have a Date table. 

I don't have a relationship set up with my data table and date table just yet. 

Some how I want to achieve an outcome where I can get a total count of starting event per month - total count of ending date per month.

For example- There are10 Start dates with the month of Jan. There are 3 ending date with the month of Jan. I would want to get the net value of 7.

This can be a sample of the data set. Each row there are two date columns including start date and end date.

ID	Name	Email	Order#	Req ID	Start Date	End Date	OrigExt
1	ABC	abc@abc.com	999	127	12-20-2021	03-20-2021	Orig

[smpa01]

@Anonymous  please provide better data.