topic Add Duration to Date - specific number of times in DAX Commands and Tips

Add Duration to Date - specific number of times

collinq — Sat, 18 Mar 2023 04:59:33 GMT

Hi all,

My issue is that I need to take a date field and with a dynamic number of days and a dynamic number of times, I need to come up with the corresponding date.

My originating table looks like this:

Key	Start Date	Duration Between Dates	# of Times
A	1/15/2023	30	3
B	2/6/2023	90	6
C	3/5/2023	45	4

For A, I will get the date 30 days from now(that being the duration column) first, then the date 30 days from that and then 30 days from that (that being the # of times field).

Results for A: (3 dates at each 30 days apart)

2/14/2023

3/16/2023

4/15/2023

For B, it is 6 times at 90 days:

Results for B: (6 dates at each 90 days apart)

5/7/2023

8/5/2023

11/3/2023

2/1/2024

5/1/2024

7/30/2024

And finally this is C:

Results for C: (4 Dates at 45 days each)

4/19/2023

5/6/2023

6/20/2023

8/4/2023

Thank you so much for your help!

Re: Add Duration to Date - specific number of times

Jihwan_Kim — Sat, 18 Mar 2023 05:57:33 GMT

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Result measure: = VAR _maxnumberoftimes = MAX ( Data[# of Times] ) VAR _indexcolumn = GENERATESERIES ( 1, _maxnumberoftimes, 1 ) VAR _generateindexcolumn = GENERATE ( Data, FILTER ( _indexcolumn, [Value] <= Data[# of Times] ) ) VAR _addresult = ADDCOLUMNS ( _generateindexcolumn, "@result", Data[Start Date] + ( Data[Duration Between Dates] * [Value] ) ) RETURN IF ( MAX ( 'Calendar'[Date] ) IN SUMMARIZE ( _addresult, [@result] ), 1 )

Re: Add Duration to Date - specific number of times

collinq — Mon, 20 Mar 2023 22:06:17 GMT

Hey Jihwan_Kim ,

THANK YOU! That did exactly what I want.

FYI for you it took a while for me to get it right because I changed the table names to mine from yours but everytime I would get either no result (no error) just no result. Or, I would get this error:

I finally figured out that I could not use my DateDim because it had a relationship with my table. Once I figured that bit out I just added your calendar version and used it and it worked fine. That is just an FYI but you answered the question that I had!