cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kan Regular Visitor
Regular Visitor

how to show months and then remaining days left as days

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.

 

image.png

 

 

image.png

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
v-jiascu-msft Super Contributor
Super Contributor

Re: how to show months and then remaining days left as days

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"

how-to-show-months-and-then-remaining-days-left-as-days

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-jiascu-msft Super Contributor
Super Contributor

Re: how to show months and then remaining days left as days

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"

how-to-show-months-and-then-remaining-days-left-as-days

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

kan Regular Visitor
Regular Visitor

Re: how to show months and then remaining days left as days

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 Super Contributor
Super Contributor

Re: how to show months and then remaining days left as days

Hi Kan,

 

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

Measure 2 = 129/30

how-to-show-months-and-then-remaining-days-left-as-days2

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
kan Regular Visitor
Regular Visitor

Re: how to show months and then remaining days left as days

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 Super Contributor
Super Contributor

Re: how to show months and then remaining days left as days

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 355 members 3,735 guests
Please welcome our newest community members: