Power Pivot Measure to calculate days between two dates for each category

[enmnm]

I hope I am in the correct forum. I have an excel table that I have loaded into my data model. The topic is, say butterflies.

The "Stage" column is a calculated column using an If statement and based on the last isblank cell. What I would like to do is to write a measure that calculates the StageDays based on Stage--which would be today - the last filled-in-date. Can you help me figure out how to do this? Thanks.

Specimen	EggDate	LarveDate	PupeDate	Stage	StageDays
01245	4/6/2023	5/7/2023	6/3/2023	Pupe
0357	2/4/2023			Egg
0934	2/9/2023	5/6/2023		Larve

[lbendlin · Accepted solution]

If you want to keep your data in Excel you will have to hard code the order of the column checks.

In Power BI we would ask you to unpivot your data, and to provide a reference table with the stage sequence index.