topic Re: Dax Measure to calculate on going project in DAX Commands and Tips

Dax Measure to calculate on going project

Anonymous — Mon, 20 Jun 2022 10:57:55 GMT

I have a project table which have Start date end date project name and i have date table that is generating calender date, i need a line chart that will show ongoing project month wise, You cant create calculated field or any extra tables

Project Name	Start Date	End date
elivie	1/11/2021	17/02/2022
abc	7/11/2021	8/3/2022
mydata	12/12/2021	7/8/2022
helloworld	11/12/2022	6/5/2022

Output

	ongoingproject
Nov-21	2
Dec-22	2
Jan-22	4
Feb-22	4
Mar-22	3
Apr-22	2
May-22	2
Jun-22	1

Re: Dax Measure to calculate on going project

Anonymous — Mon, 20 Jun 2022 11:00:03 GMT

Anonymous

Re: Dax Measure to calculate on going project

v-cazheng-msft — Thu, 23 Jun 2022 02:07:54 GMT

Hi Anonymous，

Calculated column is a necessity for you need a MMM-YY column to create your visual but it won’t generate in your model automatically.

OngoingMonth = FORMAT('calendar'[Date],"mmm-yy")

After creating this Calculated column, a Measure can achieve your needs.

OngoingProject = VAR firstDay = FIRSTDATE ( 'calendar'[Date] ) VAR lastDay = LASTDATE ( 'calendar'[Date] ) RETURN CALCULATE ( COUNT ( 'Table'[Project Name] ), FILTER ( ALL ( 'Table' ), 'Table'[Start Date] <= lastDay && 'Table'[End date] >= firstDay ) )

Sample data and the result look like this.

Also, attached the pbix file.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun