Need count of live projects as per dates

[AnirbanMahajan]

I am struggling with DAX command for generating a line graph where I need total number of live projects as per dates.

My table is as below:

I need a graph like this below:

Now, I need help on DAX where,

I should get 4 live WO if I select the range from 01 Jan 2024 to 31 Dec 2024 or

if I select the range from 01 Jan 24 to 31 Mar 24 then I should get 1 live WO or

if I selcet the range from 01 Jun 25 to 31 Aug 25 then I should get 1 live WO.

Thanks in advance

[AnirbanMahajan]

I need cumulative of all live projects at that point of time.

[Bibiano_Geraldo]

Hi @AnirbanMahajan ,
Please create a new measure using this DAX code:

LiveWorkOrders = 
VAR SelectedStartDate = MIN(_DateDim[FullDateAlternateKey])
VAR SelectedEndDate = MAX(_DateDim[FullDateAlternateKey])
RETURN
CALCULATE(
    COUNTROWS('WorkOrders'),
    'WorkOrders'[Start Date] <= SelectedEndDate,
    'WorkOrders'[End Date] >= SelectedStartDate
)

 You can use this measure in your line graph to display the number of live work orders over time.

[AnirbanMahajan]

It is not working. I need DAX which would calculate all the WO which are active before the selected date and end after the selected date.

Eg. if

WO-1 start date is 01-Jul-24 and end date is 31-Dec-24

WO -2 start date is 01-Jan-24 and end date is 31-Dec-24

WO-3 start date is 01-Dec-24 and end date is 31-Jan-25

Then,

on 01-Jan-24 I should get live number as 1

on 01-Jul-24 I should get live number as 2

on 01-Dec-24 I should get live number as 3

[dharmendars007]

Hello @AnirbanMahajan , 

Use the following DAX measure to determine the count of live Work Orders, make sure you have Date table in place.

Live Work Orders =
VAR SelectedStart = MIN('Date'[Date])
VAR SelectedEnd = MAX('Date'[Date])
RETURN
CALCULATE(
COUNTROWS('TableName'),
'TableName'[Start Date] <= SelectedEnd &&
'TableName'[End Date] >= SelectedStart)

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[AnirbanMahajan]

No, it is not working. It is not taking the count of all previous dates.

[danextian]

Hi @AnirbanMahajan 

You will need something like this:

Contracts by Time Period = 
VAR StartDate =
    MIN ( Dates[Date] )
VAR EndDate =
    MAX ( Dates[Date] )
RETURN
    COUNTROWS (
        FILTER (
            Contracts,
            Contracts[Date Start] <= EndDate
                && Contracts[Date End] >= StartDate
                && Contracts[Paid Fee] = "Yes"
        )
    )

The min and max dates are from an unrelated table.

Please see the attached pbix for details.

[AnirbanMahajan]

No, it is not working. It is not taking the count of all previous dates.

[danextian]

Hi @AnirbanMahajan 

If you want the sum of all dates, the calculation must be evaluated for each date and then summed up. Here's the updated formula.

Contracts by Time Period =
VAR StartDate =
    MIN ( Dates[Date] )
VAR EndDate =
    MAX ( Dates[Date] )
RETURN
    SUMX (
        VALUES ( Dates[Date] ),
        COUNTROWS (
            FILTER (
                Contracts,
                Contracts[Date Start] <= EndDate
                    && Contracts[Date End] >= StartDate
                    && Contracts[Paid Fee] = "Yes"
            )
        )
    )

 If this doesnt work, please provide a workable sample data (not an image) and your expected result from that.