Hi Navanita

1) Do you mean number of days between 2 dates?

2) Or how many days in January, February etc.?

Assuming its 1, you could use a datediff function

datediff('day',[first_date],[second_date])

Ifits 2, you could use a calendar

Cheers

Mark

Hi Navanita

I said it rather tongue in cheek, apologies.

Everyone using the Gregorian calendar operates with a fixed number of days per month (apart from February in a leap year) Gregorian calendar - Wikipedia, the free encyclopedia

I'm sure this isn't your question, have missed something?!

Not to but in here, but I think he/her is looking for this calculation:

DATEPART('day', DATEADD('day', -1, DATEADD('month', 1, DATETRUNC('month', [Order Date] )  ) ) )

I could be wrong.

--Shawn

DAY(DATETRUNC('month',DATEADD('month',1,[Order Date]))-1)

An alternative solution could be:

DATEDIFF('day',DATETRUNC('month', [Date]),DATETRUNC('month', dateadd('month',1,[Date])))

So you are calculating the number of days between the first day of the month and the first day of the next month. If you define the aggregation as an average, you will get something like this:

DATEDIFF('day',DATETRUNC('month', [Order Date]),DATEADD('month',1,[Order Date]))

Hi Friends,

I already follow the instructions above and still not works for me. In my case, i would like to convert the Sales in mmbtu per month and convert it into BBTUD. Doing in excel just divided the Sales in MMBTU with 1000 and the number of day in a certain month.

Please advice how to solve this problem...

Hi Mark,

I am working on a projects tracking dashboard and I need to find out how many projects are ending in the next 30 days and how many are ending in 60 days..I have an actual start date,actual end date,estimated end...I am confused as to how to get the results..I needs to give me the number of projects ending in those days.

Simple and practical solution

Hi,

1) No of Days left in current month?

    Create 2 calculated fields like Last Day & Days Left

    Last Day : DATEADD('day',-1, DATEADD('month',1, DATETRUNC('month', TODAY())))

    Days Left : DATEDIFF('day', TODAY(),[Last Day])

2) No of Days completed in current month?

    Create 1 more calculated field with Days Completed

    Days Completed: DATEADD('day', DATEPART('day',TODAY()), [Last Day])

hope this may help you guys.... Thanks

Hi Pablo,

I realize this scenario is a little different than the original question, but I was wondering if I could pick your brain on how to make this formula dynamic to adjust to quarter or year, depending what is in the view?  I am looking for a formula that will calculate the number of days within the time period shown, which will change when you drill into the date hierarchy from year to quarter to month. 

Thanks for considering!

Kim,

Pablo's formula works for quarters or years as well if you just change the 'month' part to quarter or year.

DATEDIFF('day',DATETRUNC('month', [Date]),DATETRUNC('month', dateadd('month',1,[Date])))

The dynamic part might require a parameter instead.

Richard

I had a similar situation but didn't actually have a proper date to go off of, just a month number 1-12.  This is how I approached it:

CASE [Month Number]

WHEN 1 THEN 31

WHEN 2 THEN IF [Is Leap Year] THEN 29 ELSE 28 END

WHEN 3 THEN 31

WHEN 4 THEN 30

WHEN 5 THEN 31

WHEN 6 THEN 30

WHEN 7 THEN 31

WHEN 8 THEN 31

WHEN 9 THEN 30

WHEN 10 THEN 31

WHEN 11 THEN 30

WHEN 12 THEN 31

END

Where [Is Leap Year] is just:

[Year] % 4 = 0