Cumulative number of projects with end-date

DAX DAX Calculations
1

Hi all,

I have been searching through various blogs and forums, but I cant seem to find the answer to my problem.

My dataset consists of projects, start- and end date. Table below:

Projects Start date End date
A 01.01.2024 31.07.2024
B 01.02.2024
C 01.03.2024
D 01.04.2024
E 01.05.2024
F 01.06.2024
G 01.07.2024
H 01.08.2024
I 01.09.2024

I want to display the cumulative number of ongoing projects on a graph like this:

image
image1021×452 5.96 KB

I have a dedicated date-table that have an active relationship to the start-date and an inactive relationship to the end-date.

I manange to get the number of cumalitve ongoing projects, but the end-date appears to be at the start-date, but I cant seem to incorporate the inactive relationsship into the DAX. E.g. the project that ended on July 31st looks to have ended on January 1st.

The DAX I have tried is this:

Ongoing projects = 
VAR MaxDate = MAX(Calender[Date] )

RETURN
CALCULATE(
    DISTINCTCOUNT(table1[Projects]), 
    Calender[Date] <= MaxDate,
    ISBLANK(table1[EndDate])

I’m sure there must be an “easy” way to solve this, but can’t wrap my head around how.

Cheers

1 Like
2

Hi @Magn,

While waiting for the community to review and respond to your issue, take advantage of Data Mentor . It offers a wealth of tools and resources that could provide immediate solutions and enhance your report-building efficiency.

Suggested link: [https://mentor.enterprisedna.co/explain-simply]

image
image1540×966 104 KB

Cheers,

Enterprise DNA Support Team

3

Hello @Magn - Can you try below. Seems to be working for your sample data.

Ongoing projects = 
VAR MaxDate = MAX(Calender[Date] )
var MinDate = min(Calender[Date])

RETURN

CALCULATE(
    DISTINCTCOUNT(table1[Projects]), 
    table1[Start date] <= MaxDate,OR(ISBLANK(table1[End date]),table1[End date] >= MinDate),ALLSELECTED(table1))

Thanks
Ankit J

4

Hi @ankit ,

Thanks for your reply. I tried the DAX you suggested but it gave me a flat line. However, I tried the formula that was suggested by the Enterprise DNA mentor:

Ongoing projects = 
VAR MaxDate = MAX(Calender[Date])

RETURN
CALCULATE(
    DISTINCTCOUNT(table1[Projects]), 
    Calender[Date] <= MaxDate,
    table1[StartDate] <= MaxDate, 
    (ISBLANK(table1[EndDate]) || table1[EndDate] > MaxDate)
)

This did the trick.

Thanks again for your suggestion.

Cheers,
Magn

1 Like