Simulating Multiple Rows when Only 1 Row

[bc5891]

Good morning,

I have a table similar to below:

ID	OPEN DATE	CLOSE DATE
123	2016	2019

In the front end I want to show this ID across multiple years and provide it different statuses.  There could also be multiple statuses per year.

Essentially in the front end I want to somehow create the following :
It would be in a bar chart ideally.
Rules:

Open in current year = New
Open in current year and not closed in that year = Ongoing
Not opened in current year and not closed in current year = Ongoing
Closed in Current Year = Closed

It would give the output like below (in an on the fly table).

ID	YEAR	STATUS
123	2016	NEW
123	2016	ONGOING
123	2017	ONGOING
123	2018	ONGOING
123	2019	CLOSED

Without doing this in the script is it possible to do this in an expression?  could rangesum be used?
The reason for not doing it in the back end script is that this could massively inflate the data set if we go down to month level over time.

Thanks

[Or]

If it's just the one value, you could do this with ValueList() to create a synthetic dimension. However, if you have multiple IDs and each has different years, I'm not sure if this'd work (it might - you'd have to write a measure that returns null of the ValueList() result isn't in the range, and then hide zero/null).

This shouldn't have too much of an impact on the app size if you do it in script, most likely, and that's easy to handle using While.

[bc5891]

Thanks, there are many Ids.

Do you have a simple example of a while loop referencing the ID here?

[Or · Accepted solution]

Load ID, OPENDATE + IterNo() - 1 as Year, If(Something=SomethingElse,'Status1','Status2') as Status

From YourTable

While OPENDATE + IterNo() - 1 <= CLOSEDATE;

[bc5891]

thank you very much

[bc5891]

1 more question if I may - I've been playing about with this and I have a similar request using Quarter but obviously the iterno() will not work in this case.    can the same/similar method be used?

ID	QTRSTART	QTREND		ID	QTRSTART	QTREND	QTR
123	20211	20222		123	20211	20222	20211
				123	20211	20222	20212
				123	20211	20222	20213
				123	20211	20222	20214
				123	20211	20222	20221
				123	20211	20222	20222

[Or]

IterNo() will still work, you'll just need to use a different formula to generate your quarters. Something like AddMonths(Start,(IterNo-1)*3 would generate quarters from a starting quarter, assuming the starting quarter is a date.