First I tried to make a measure, and that didn't work, and then I tried a calculated column, and that didn't work either b/c I am mixing text and dates.
In a table named "Log," I have three columns called Log[EggStart], Log[LarveStart], and Log[PupaStart]. Each has a date when they start their particular stage--this table is updated daily. So on any given day, not all columns may have a date.
Example:
Specimen EggStart LarveStart PupaStart
0256 12/31/2023 2/4/2024 5/3/2024
1405 3/4/2024 4/1/2024
0382 5/1/2024
My calculated column in the PowerPivot section has a column called "Stage," which calculates the current stage based on the above dates (and what is blank.)
Now, I want to calculate the current stage days. So if Log[Stage]="Egg", then today()-Egg[Start]. However, this totally isn't working. Can you help?
The measure I came up with (that didn't work either) was:
StatusDate:=Calculate(DateDIff(Log[EggStart],Today(),DAY),Log[Stage]="Egg")
