topic How to convert days into month in App Development

How to convert days into month

abc_18 — Sat, 16 Nov 2024 03:18:25 GMT

Hello,

I have to create a measure where I need to divide the sum(sales) by no of months passed.

Data is based on fiscal year, so if we are considering FY20, then it should count Apr-19 to Jan-20, total 10 months, plus it should convert the no of days passed in Feb to months, so around 10.3. how to achieve that.

Below is the expression, in which I have to consider above condition, please suggest, currently I am just taking max year, current month.

=(SUM({$<fYear={'$(vMaxYear)'},FMonth={'$(vMaxMonth)'}>}sales) / count(distinct ro_code))/1000

Re: How to convert days into month

Kashyap_R — Tue, 11 Feb 2020 12:39:12 GMT

Can u please elaborate do want to divide Sum(sales) by number why are you considering count(id)

If(you want the only latest year sales)

for month calculation you can use this

Count({<Date={">=$(=YearStart(Max(Date)))<=$(=Monthend(Max(Date),-1))"}>}distinct Month)
+
(Count({<Date={">=$(=Monthstart(Max(Date)))<=$(=(Max(Date)))"}>}distinct Day)
/
(Monthend(Max(Date))-Monthstart(Max(Date))))

Thanks

Re: How to convert days into month

abc_18 — Wed, 12 Feb 2020 10:06:29 GMT

Hi Kashyap,

Thank you for the response, I tried the below expression and for days passe, for current month it's working fine, for ex:-

If I select FY 19-20, then it should consider 10 months completed+No of days passed in Feb month =around 10.3

But if I select FY 18-19, it should give me 12 months, but it's giving me 12.3, I tried different expression but still not working for me, can you please suggest?

Regards

Re: How to convert days into month

Kashyap_R — Wed, 12 Feb 2020 10:47:36 GMT

Try this change the year according to fiscal year

if(Year(Max(Date))=Year(Max(All Date)),
(Count({<Date={">=$(=YearStart(Max(Date)))<=$(=Monthend(Max(Date),-1))"}>}distinct Month)
+
(Count({<Date={">=$(=Monthstart(Max(Date)))<=$(=(Max(Date)))"}>}distinct Day)
/
(Monthend(Max(Date))-Monthstart(Max(Date))))),
Count({<Date={">=$(=YearStart(Max(Date)))<=$(=Yearend(Max(Date)))"}>}distinct Month))

Hope this helps

Thanks

Re: How to convert days into month

abc_18 — Thu, 13 Feb 2020 07:36:57 GMT

Hi Kashyap,

Thank you very much, it's working as expected.

Regards