Dax Measure to calculate on going project

[Anonymous]

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

[v-cazheng-msft · Accepted solution]

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

[Anonymous]

@Anonymous