Forum Discussion

Dor-Y13's avatar
Dor-Y13
Frequent Visitor
2 years ago

Adding end date in row

Hello friends, My goal is to make an SCD calculation for examining Profits over time for different Items. 1. first step i need help with is adding End Date column for this table[table name = "Price...
Need Help
Dor-Y13's avatar
Dor-Y13
Frequent Visitor
2 years ago

HI isjoycewang 
for my SCD calculations i want to have also the last end date (which is now appears to be blank).

I have treid to add IF with ISBLANK in order to establish that but the output is only the end date i inserted in the Formula as the future end date ("31/12/2050").

 

EndDate UPG2 = 
VAR _Index = [Index]+1
VAR _Id = [ItemKey] 
RETURN
                    if(ISBLANK(
                                CALCULATE( 
                             MAX(PricesForSCD[DatF - StartDate]), 
                             FILTER(ALL(PricesForSCD),
                             PricesForSCD[Index] = _Index && [ItemKey] = _Id ))),
                        "31/12/2050")

 

 

the out come is that:

 

Could you suggest for a soultion so the calculated formula will insert the value and not Blanks?
I am off course can aggregate the columns but i wish to understand why it doesnt work now

 

Many thanks

isjoycewang's avatar
isjoycewang
Solution Supplier
2 years ago

Hi Dor-Y13,

 

Sorry for the late response. Been too busy lately.

I think it would be easier to assign an end date for the latest one, which means the biggest index.

EndDate = 
VAR _Index = [Index]+1
VAR _Id = [ID]
VAR _MaxIndex = CALCULATE( MAX('Table'[Index]), FILTER(ALL('Table'), [ID] = _Id))
RETURN 
IF( [Index] = _MaxIndex, DATE(2050,12,31), 
CALCULATE( MAX('Table'[StartDate]), FILTER(ALL('Table'), 'Table'[Index] = _Index && [ID] = _Id )))

 

 

Best Regards,

Joyce