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

[v-mdharahman]

Hi @AnirbanMahajan,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-mdharahman]

Hi @AnirbanMahajan,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-mdharahman]

Hi @AnirbanMahajan,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help?

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

Thank you for your patience and look forward to hearing from you.

[v-mdharahman · Accepted solution]

Hi @AnirbanMahajan,

Thanks for reaching out to the Microsoft fabric community forum.

Looks like you’re trying to build a line chart that dynamically count live work orders over time, based on whether each date falls between the Start Date and End Date of a Work Order. You’re on the right track, here's how you can approach this in Power BI:

* Create a calendar table (if not already present), this will help you plot values over time. "Calendar = CALENDAR(MIN('WorkOrders'[Start Date]), MAX('WorkOrders'[End Date]))", make sure this Calendar table is marked as a Date Table and has a relationship with your slicer/date axis.

* Now create a measure to count live work orders. Here’s the DAX measure you can use:
Live WOs =
VAR SelectedDate = MAX('Calendar'[Date])
RETURN
CALCULATE(
COUNTROWS('WorkOrders'),
'WorkOrders'[Start Date] <= SelectedDate &&
'WorkOrders'[End Date] >= SelectedDate
)

This measure calculates the number of WOs that are live on each date in your calendar.

* Now build the Line Chart with Axis- 'Calendar'[Date] andValues- Live WOs (the measure above). You can now add slicers or set the X-axis range to filter down (e.g., Jan–Dec 2024 or Jan–Mar 2024) and the line graph will dynamically adjust to show how many WOs were live during that period.

Best Regards,
Hammad.
Community Support Team.

[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.