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 = "PriceLists"] that will be calculated according to the next rows start date (Prefferbly with as simple as possible DAX). please note i filtered one Item key for make it clear to understand the data structure.

 

 

 

2. Second step will be creating a Measure that will use the correct Unit cost ("Price") that was sold during the relevant period (e.g. if the cost was 100 since Jan to March and then 120 until June, so Feb sales will be calculated based on cost = 100). if you could help with guidance to Dax formula, or i could upload later the relevant Table (after phase 1 will be done)

 

Would appreciate your help very much,

Thanks a lot!

 

 

 

 

Need Help

4 Replies

  • 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

       

  • 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