0 if Not started

[AllanBerces]

Hi good day can anyone help me on my table, what i required is that if the Plan Hrs column not yet started the earned column should be 0. 

My Earned column code:

Earned =

CALCULATE (

    SUM ( 'Table'[Earned] ),

    FILTER (

        ALL ( 'Table' ),

        'Table'[Progress Date] = EARLIER ( 'Tabl02'[PlanDate] )

            && 'Table'[Location] = EARLIER ( 'Tabl02'[TaskNo] )

            && 'Table'[Mode] = EARLIER ( 'Tabl02'[A -Mode] )

)

)

RESULT : since my project will start only on May 05, 2025

Thank you

[v-tsaipranay]

Hi @AllanBerces ,

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.

[Ashish_Excel]

Hi,

Share some raw data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[v-tsaipranay]

Hi @AllanBerces ,

Thank you for reaching out to the Microsoft Fabric Community Forum.

Based on your requirement, you would like the Earned value to be set to 0 until the project start date (May 5, 2025) and then calculate the earned value using the existing logic from that point onward.

To achieve this, you can modify your Earned measure by adding a condition that checks whether the PlanDate is before the start date. Below is an updated version of your Earned measure:

Earned =
IF (
    MAX('Tabl02'[PlanDate]) < DATE(2025, 5, 5),
    0,
    CALCULATE (
        SUM ( 'Table'[Earned] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Progress Date] = EARLIER ( 'Tabl02'[PlanDate] )
                && 'Table'[Location] = EARLIER ( 'Tabl02'[TaskNo] )
                && 'Table'[Mode] = EARLIER ( 'Tabl02'[A -Mode] )
        )
    )
)

With this modification: If the PlanDate is earlier than May 5, 2025, the Earned value will be set to 0. If the PlanDate is on or after May 5, 2025, the existing logic will calculate the Earned value as expected.

Alternatively, if you would prefer a more dynamic approach, where the comparison is based on today’s date rather than a fixed date, you can use the following formula:

Earned =
IF (
    MAX('Tabl02'[PlanDate]) < TODAY(),
    0,
    CALCULATE (
        SUM ( 'Table'[Earned] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Progress Date] = EARLIER ( 'Tabl02'[PlanDate] )
                && 'Table'[Location] = EARLIER ( 'Tabl02'[TaskNo] )
                && 'Table'[Mode] = EARLIER ( 'Tabl02'[A -Mode] )
        )
    )
)

This version uses the TODAY() function to dynamically calculate the Earned value based on the current date.

I hope this will resolve your issue, if you need any further assistance, feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

[AllanBerces]

Hi @v-tsaipranay thank you very much for the reply but when I try the solution, still some value reflected on my Earned column

[v-tsaipranay]

Hi @AllanBerces ,

Thank you for your feedback, and I appreciate you trying out the suggested solution.

Based on your clarification, it seems the core condition for showing Earned = 0 is tied directly to the Plan Hrs column specifically when the value is zero (indicating the task has not started). The previous logic based on date may not fully address this.

Please try the following revised DAX measure which includes a check on Plan Hrs:

Earned =
IF (
    'Tabl02'[Plan Hrs] = 0,
    0,
    CALCULATE (
        SUM ( 'Table'[Earned] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Progress Date] = EARLIER ( 'Tabl02'[PlanDate] )
                && 'Table'[Location] = EARLIER ( 'Tabl02'[TaskNo] )
                && 'Table'[Mode] = EARLIER ( 'Tabl02'[A -Mode] )
        )
    )
)

With this update:

• If Plan Hrs = 0, the Earned column will return 0.
• Otherwise, it will compute the earned value as per your existing logic.

Please let me know if this now aligns with your expectations or if further adjustments are needed.

If this helps, kindly consider marking this as a solution for future reference to others as well.

Thank you!

[v-tsaipranay]

Hi @AllanBerces ,

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.

[AllanBerces]

Hi @v-tsaipranay thank you for the reply, but unfortunately there are times my plan is 0 but they execute the job early that need to reflect on my earned hrs.

[v-tsaipranay · Accepted solution]

Hi @AllanBerces ,

You're right, using Plan Hrs = 0 to decide if a task has started doesn't always work, especially if work begins early without planned hours.

So instead of checking Plan Hrs, we should only return 0 in the Earned column if no actual work has been recorded yet.

Here’s an updated measure that does just that:

Earned =
VAR EarnedCalc =
    CALCULATE (
        SUM ( 'Table'[Earned] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Progress Date] = EARLIER ( 'Tabl02'[PlanDate] ) &&
            'Table'[Location] = EARLIER ( 'Tabl02'[TaskNo] ) &&
            'Table'[Mode] = EARLIER ( 'Tabl02'[A -Mode] )
        )
    )
RETURN
IF (
    ISBLANK(EarnedCalc),
    0,
    EarnedCalc
)

This will show 0 only if no Earned hours are found. If any work is done, it will show the actual value.

If the issue still persists after doing the above things, please provide sample data as mentioned by @Ashish_Excel .

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

[AllanBerces]

Hi @v-tsaipranay thank you very much for the reply, all goods

[v-tsaipranay]

Hi @AllanBerces ,

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.

[AllanBerces]

Or can update my code the Earned value will captured only when my project start  on May 5, 2025 onward.