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
isjoycewang's avatar
isjoycewang
Solution Supplier
2 years ago

Hi Dor-Y13,

 

Demo file attached.

Please try below steps:

1. Define Index for each ID in the Power Query editor

 - sort rows by [startdate]

 - group by [ID]

 - add a new custom column on [Count] 

= Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index", 1,1))

  - remove [Count]

  - expand [Index] group

 

2. Add calculated column in the table

EndDate = 
VAR _Index = [Index]+1
VAR _Id = [ID]
RETURN CALCULATE( MAX('Table'[StartDate]), 
FILTER(ALL('Table'), 'Table'[Index] = _Index && [ID] = _Id ))

3. Create the [Measure] for price

Price_Measure = 
CALCULATE( MAX('Table'[Price]), 
    FILTER(ALL('Table'), 
    SELECTEDVALUE('Table'[ID]) = [ID] && 
    SELECTEDVALUE('Date'[Date]) > [StartDate] && (SELECTEDVALUE('Date'[Date]) <= [EndDate] || ISBLANK([EndDate]))
    )
)

 

Best Regards,

Joyce

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

    Hi Joyce, thanks for your help.

    for some reason the part with the Measure doesnt work for me, could you might help with that?
    The measure has no Error, however it has only blank values for every possible date so somthing seems to be wrong (the Dim_pricesforSCD is connected to the Dim_Items table).

    Price_Measure = 
CALCULATE( MAX(PricesForSCD[Price]), 
    FILTER(ALL(PricesForSCD), 
    SELECTEDVALUE(PricesForSCD[ItemKey]) = [ItemKey] && 
    SELECTEDVALUE('Dates'[Date]) > [DatF - StartDate] && (SELECTEDVALUE('Dates'[Date]) <= [EndDate] || ISBLANK([EndDate]))
    )
)

     

    that is a pic of the table visual:

     

    I saw that in the demo you attached the connection between the Dates and the Dim table was not turned on, is that on purpose?

     

    Hope you could help figure it out,

    Many thanks

    Dor