how to show months and then remaining days left as days

[kan]

Hi I have a stacked column chart 

Hi I have a stacked column chart  which represents average  months that are required to complete tasks for each region.some has values =18.68(which means 18 months and remaining days).I want to show it in visualisation as example 18months,21days instead of 18.78.If this possible Can someone please help.

Thanks

[v-jiascu-msft · Accepted solution]

Hi @kan,

Since 18months,21days isn't a number, it can't be added to the Value field. I would suggest adding it as a tooltip. Please refer to the snapshot below.

Measure =
VAR avgMonth =
    AVERAGE ( Table1[MonthsThruInterval] )
VAR intMonth =
    INT ( avgMonth )
VAR intDay =
    MOD ( avgMonth, intMonth ) * 30
RETURN
    intMonth & " Months "
        & intDay
        & " Days"

Best Regards,
Dale

[kan]

Thanks Dale,It worked for me.Also can you please let me know if there is any dax to calculate no.of months from given total no.of days.

Example :If no .of days=129 then months would be 129/30=4.3.But I am looking if any dax function is available to calculate this.

Thanks

Kan.

[v-jiascu-msft]

Hi Kan,

That's a basic math calculation. You can calculate it directly like below.

Measure 2 = 129/30

Best Regards,
Dale

[kan]

Ya I know its a math calculation,but there are months which has 30 days and some 31 days.so thought if there is any function for that.May be i will consider to divide with (365/12=30.4) instead of 30 on an average to do calculation. Thanks.

[v-jiascu-msft]

Hi @kan,

I didn' aware of any function to do that. That would be complicated. For example, days from 5th Feb to 10th Apr. So the calculation process depends on your desired precision.

Best Regards,
Dale