Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Dor-Y13
Frequent Visitor

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.

 

DorY13_0-1702325478414.png

 

 

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!

 

 

 

 

4 REPLIES 4
Dor-Y13
Frequent Visitor

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:

DorY13_0-1703076040515.png

 

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

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 )))

 

isjoycewang_0-1703140432530.png

 

Best Regards,

Joyce

isjoycewang
Super User
Super User

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 ))

isjoycewang_0-1702374916877.png

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]))
    )
)

isjoycewang_1-1702374988910.png

 

Best Regards,

Joyce

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:

DorY13_0-1702553821172.png

 

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors