How to convert days into month

[abc_18]

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

[Kashyap_R]

Hi

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

or

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

[abc_18]

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

[Kashyap_R · Accepted solution]

Hi

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

[abc_18]

Hi Kashyap,

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

Regards