How to expand table results for a date range depending on the later of two different date columns

[Anonymous]

Hi,

I'm trying to expand a data table I've brought into Power BI.  I've done this before but I'm now running into a situation where some results do not have an end date.  Because of this, my created column is erroring out for the results that do not have an end date:

My current custom column formula is:

Date Range = { Number.From([EFFDATEMM])..Number.From([TRMDATEMM]) }

However, because some results do not have a TRMDATEMM date listed...these results are erroring out.

My solution was to bring in a column to the table that states today's date (ASOFDATE).  This way, the expansion will have an end date for results even without a TRMDATEMM value.

I'd like the new formula to essentially create a result for every day between the EFFDATEMM and TRMDATEMM...like it is already doing...but to also consider and to create a new result for every day between the results that have an EFFDATEMM field and the ASOFDATE field I've just brought in.

However, I'm not sure of the formula to write in order to accomplish this.

Is there a way I can build out the current formula to complete this task?

{ Number.From([EFFDATEMM])..Number.From([TRMDATEMM]) }

[Greg_Deckler]

Agree with @HotChilli Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

My work around was to create an additional conditional column in Edit Queries.  It's not the cleanest way to accomplish it but it is working.

[HotChilli]

If you provide some data, it always helps to solve the problem.

How about

First : replace the empty values in TRMDATEMM with null (you can do this from the Transform menu)

then

if [TRMDATEMM] = null then { Number.From([EFFDATEMM])..Number.From([ASOFDATE]) } else { Number.From([EFFDATEMM])..Number.From([TRMDATEMM]) })

edit: On re-reading, i suppose this is not really what you want. It would solve the initial problem but this part is confusing:

"I'd like the new formula to essentially create a result for every day between the EFFDATEMM and TRMDATEMM...like it is already doing...but to also consider and to create a new result for every day between the results that have an EFFDATEMM field and the ASOFDATE field I've just brought in."

Can you show an example please