topic Re: Create Months Columns to Calculate number of days between 2 dates in DAX Commands and Tips

Create Months Columns to Calculate number of days between 2 dates

que — Tue, 14 Feb 2023 16:57:58 GMT

I have 2 dates: Start Date = March 23, 2023 End Date = March 23, 2025. I can calculate total number of days for the term - 731, count between Start and end which allows be to calc a daily revenue, is there a way to have BI recognize the months individually to allow for a monthly calc on revenue. Because the dates don't always start on the 1st and end on the last day of the month I can't quite get the logic on how to make this work. It can work with a date count table for the full months but not partial.

March 2023 = 8 x daily revenue

April 2023 = 30 x daily revenue

May 2023 = 31 x Daily revenue

Re: Create Months Columns to Calculate number of days between 2 dates

BrianConnelly — Tue, 14 Feb 2023 18:09:03 GMT

So, you need to provide some sample data and expected results. The short answer is yes, but there is not enough context to your question for the community to help you based on what has been provided. In most cases we would be using variables also we would need to know how the measure and visual would be applied.

Re: Create Months Columns to Calculate number of days between 2 dates

que — Tue, 14 Feb 2023 18:22:10 GMT

Sorry, I am new! I hope I did this right. I can make it work in excel using: =IF(MEDIAN(G$1,H$1,$F2)=$F2,((H$1-$F2)/DAY(H$1))*$D2,IF(MEDIAN($E2,$F2,H$1)=H$1,IF(MEDIAN($E2,G$1,H$1)=$E2,(H$1-$E2)/DAY(H$1)*$D2,$D2),0)). This calculates the first month (December, from the actual start date. Does this make sense? Just trying to replicate in BI and get this out of excel.

Deal Name	Duration (Months)	Duration Days	Expected Revenue	Start Date	Contract End Date	31-Oct-22	30-Nov-22	31-Dec-22	31-Jan-23
Deal One	12	365	$ 5,000.00	Dec 15, 2022	12/15/2023		$ -	$ 2,581	$ 5,000
Deal Two	12	365	$ 37,500.00	May 12, 2023	5/11/2024		$ -	$ -	$ -
Deal Three	12	365	$ 25,000.00	Feb 10, 2023	2/10/2024		$ -	$ -	$ -

Re: Create Months Columns to Calculate number of days between 2 dates

BrianConnelly — Wed, 15 Feb 2023 02:34:54 GMT

Does this look right to you?

Re: Create Months Columns to Calculate number of days between 2 dates

que — Wed, 15 Feb 2023 13:30:03 GMT

Hi Brian, indeed it does look correct. I just can't seem to translate that same formula in BI.