Given the date field as mm-yyyy, is there a simple way to compute the total number of days in the month?
I have a series as 01-2021, 02-2021, 03-2021 etc. I wanted to compute # of days as 31, 28, 31 etc.
Hi @Arvind_Shastry - I believe there is NO direct function available which can tell total days of a month. You can create a calculated field, only trick part is the Feb month, remaining months, days are fixed. Try to explore if else statement and create a calculated field for the same.
Hi @David_Wong @Naveed @Biswajit_1993 - Any expert advise on this?
Regards - Sanjeeb
2 Likes
Hi @Arvind_Shastry - You can use convert the field into date and use dateDiff on it to get the number of days in the month. See calcs below.
Derived Date
parseDate(concat('01-',{Month-Year String}),'dd-MM-yyyy')
Days in Month
dateDiff({Derived Date}, addDateTime(1,'MM',{Derived Date}), 'DD')
This will be calculated at each data record level. So, it can be included in dataset and can be pre-calculated if you are using SPICE. Since we are using dateDiff, we will get right results for leap years as well.
Iām marking this as solution to your question. Please let us know if you have further questions.
Regards,
Arun Santhosh
3 Likes
Hi,
Thank you for this. I have to find monthly run rate of current month.
That will be daily average of current month/no of days of current month. How can I write a dynamically changing formula? The data is refreshed daily.
Appreciate your help