Cannot remove decimal places

[ArchStanton]

Hi,

This is driving me mad, hope someone can help!

The code below defaults the Average Months to 2 decimal places - I don't want any decimals.

Case Length (Adj) Mths = 
IF (
    'Cases'[statecode] = "Active",
    DATEDIFF (
        IF (
            ISBLANK ( 'Cases'[legacycasecreationdate] ),
            'Cases'[Created On],
            'Cases'[legacycasecreationdate]
        ),
        NOW (),
        MONTH
    ),
    DATEDIFF (
        IF (
            ISBLANK ( 'Cases'[legacycasecreationdate] ),
            'Cases'[Created On],
            'Cases'[legacycasecreationdate]
        ),
        'Cases'[Resolution Date],
        MONTH
    )
)

I have tried so many solutions and none work.

[Daniel29195]

@ArchStanton 

use format (  your_measure_code , "##")

let me know if this works .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[ArchStanton]

Hi,

Using FORMAT breaks the code and my Average average disappears: 

Case Length (Adj) Mths = FORMAT(
IF (
    'Cases'[statecode] = "Active",
    DATEDIFF (
        IF (
            ISBLANK ( 'Cases'[legacycasecreationdate] ),
            'Cases'[Created On],
            'Cases'[legacycasecreationdate]
        ),
        NOW (),
        MONTH
    ),
    DATEDIFF (
        IF (
            ISBLANK ( 'Cases'[legacycasecreationdate] ),
            'Cases'[Created On],
            'Cases'[legacycasecreationdate]
        ),
        'Cases'[Resolution Date],
        MONTH
    )
),"##")

[PijushRoy]

Hi @ArchStanton 

Please use a measure 

Measure = FORMAT([YourMeasure],"##")

Let me know if that works for you

If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

Thanks
Pijush
Linkedin

[Daniel29195]

@ArchStanton 

convert(format( your_measure, "##" ) ,integer) 

pattern : 

[ArchStanton]

Thanks Daniel, I managed to get the answer from the other helper on this thread, I was dealing with a calculated column not a Measure.

Thank you for helping anyway.

[ArchStanton]

Hi, I've responded to Daniel above who proposed the same solution. It does not work as I am summarising the calaculated column by 'Averaging'.

FORMAT breaks the code and I do not have the option to see a Summarised value anymore only Counts

[PijushRoy]

Hi @ArchStanton 

Dont change your existing measure
Create a another measure use 

Measure = FORMAT([Case Length (Adj) Mths],"##")

If your requirement is solved, please mark THIS ANSWER as SOLUTION

[ArchStanton]

Case Length (Adj) Mths is a calculated column not a measure

It was initially calculating DAYS but I created a copy and changed the Parameter to MONTH earlier today

[PijushRoy · Accepted solution]

@ArchStanton 

Measure = FORMAT(SUM(Table[Case Length (Adj) Mths]),"##")

[ArchStanton]

Hi,

Thanks for helping me with this.

It didn't work initially because the Calculated Column is summarised as an Average.

I removed this from the column, substituted SUM for AVERASGE and it now works with no decimal places 😊

It's incredible that so much effort and resource is required to do a simple thing like this - maybe its something for the ideas board?!

Thanks for your help!