How to find number of days in a date range for each month in dax

0
votes

I need to calculate, using DAX (Data Analysis eXpressions), how many days within a given range fall into each calendar month.

I am given 2 dates, which define a date range; for example 2016-07-01 to 2016-08-03. I need to find how many days in that range fall in to each month i.e. how many fall into July, and how many into August.

In the example given, the expected result is 30 days in July and 2 days in August.

1
excelpowerbidax
I need to use dax for this – Himanshu Malik
I have rephrased your question to improve readability. If you would like to edit it further, please do - on this site users are encouraged to edit and re-edit questions to make them as clear and useful as possible. – Vince Bowdren

1 Answers

0
votes

Assuming your dates are called "Date1" and "Date2" you could calculate the number of days in the second month as:

DaysInMonth2 = 1 + Date2 - MAX(Date1, DATE(YEAR(Date2),MONTH(Date2),1)

The Max is required in case the Date1 is on the same month

DaysInMonth1 = MAX(0,DATE(YEAR(Date2),MONTH(Date2),1)-Date1)

This second formula would calculate the number of days in the 1st month, and again in the case in which Date1 and Date2 are on the same month DaysInMonth1 would be 0.