Help with adding if then statement?

[SPDA]

Hello,

I am new to creating measures and have hit a wall. Can anyone help?

I've created a measure that works properly to calculate the sum of a field called AgrHrsCovered for Jan 2025 only.

I need to add a caveat that if the hours are less than 1 to return 1 as the sum. If 1 or more then follow the calculation for sum. 

Thank you!

Jan 2025 Hrs =

CALCULATE (

    SUM ('EHR Hours'[AgrHrsCovered] ),

    'EHR Hours'[Date Start] >= DATE(2025,1,1) &&

    'EHR Hours'[Date Start] <= DATE(2025,1,31)

)

[v-pnaroju-msft]

Thank you, @wardy912, @mfed, for your response.

Hi SPDA,

We appreciate your inquiry raised through the Microsoft Fabric Community Forum.

Based on my understanding of the issue, please find attached a screenshot along with a sample PBIX file, which may assist in resolving the problem:

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will help other community members who may have similar queries. However, if the response does not address your requirements, please provide sample data that clearly illustrates your issue or question in a usable format (and not as a screenshot). Kindly ensure that the data shared does not contain any sensitive or unrelated information. Additionally, please indicate the expected outcome based on the sample data provided.

Should you have any further questions or concerns, please feel free to reach out to the Microsoft Fabric Community.

Thank you.

[SPDA]

Hey, thanks, wardy912. That looks like it makes sense to me, but when I compared the new (TEST Jan 2025 Hrs)  to the original (Jan 2024 Hrs) it isn't doing what I want:

[wardy912 · Accepted solution]

Can you provide a sample of the data please? It may be better to use a calculated column that only performs the if statement, depending on how your data is presented

Here i've used the column
CalculatedColumn = IF('January2025Hours'[Hours] < 1, 1, 'January2025Hours'[Hours])

to obtain the following results

Let me know if this fixes the issue, thanks

[mfed]

Agree with Wardy 912.

Just for another angle on this same solution, you would probably have a measure for your SUM of EHR hours anyway, (same as the Variable created above and so you can use it elsewhere), so you can then write the same IF statement using that measure in the same way as the variable is used above. Just another way of looking at it. 

[SPDA]

Thanks for the input! I wasn't sure if I could reference measures in measures.

[mfed]

Yes, for sure - in fact in my training it was best practice to create a 'Measure Table' with as many measures as you thought you might need, then you can reference them on their own or in other measures. It's a standalone table in the data model with all your individual measures, and once you have that it sits right at the top of your 'Data' section on your main screen.

[SPDA]

Nice, I'll add that to my practice. Appreciate the tip!

[wardy912]

Hi @SPDA 

Jan 2025 Hrs = 
VAR TotalHrs = 
    CALCULATE (
        SUM ('EHR Hours'[AgrHrsCovered]),
        'EHR Hours'[Date Start] >= DATE(2025,1,1) &&
        'EHR Hours'[Date Start] <= DATE(2025,1,31)
    )
RETURN
    IF (TotalHrs < 1, 1, TotalHrs)

The return statement gives 1 if true, the actual sum if false.

Hope this helps, please mark as the solution and give a thumbs up if it does, thanks!