Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

Dax Measure to calculate on going project

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 NameStart DateEnd date
elivie1/11/202117/02/2022
abc7/11/20218/3/2022
mydata12/12/20217/8/2022
helloworld11/12/20226/5/2022

 

Output

 ongoingproject
Nov-212
Dec-222
Jan-224
Feb-224
Mar-223
Apr-222
May-222
Jun-221
1 ACCEPTED SOLUTION
v-cazheng-msft
Community Support
Community Support

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.

vcazhengmsft_0-1655949985944.png

 

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

View solution in original post

2 REPLIES 2
v-cazheng-msft
Community Support
Community Support

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.

vcazhengmsft_0-1655949985944.png

 

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
Not applicable

@Anonymous 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.