calculate column based on the date value of that row

[Anonymous]

hello all,

I'm struggeling with writing the code for a calculated column that sums a column based on another value in each row. As i can't explain this very well in words, I made an example:

#	Date & Time	Weight	Category	Result
1	28-7 8:00	10	A	10
2	29-7 10:00	20	A	30
3	29-7 12:00	30	B	30
4	31-7 15:00	50	B	120
5	30-7 12:00	40	B	70

I wanted to write something for the result column. This column should sum all the weights of the same category before that moment. In the case of entry #4, it should sum all the weights of entries of category B on or before 31-7 15:00,. which in this example results in 30+40+50.

I have tried some functions as DATAADD and SUMX etc, but couldn't figure it out. I'm hoping someone here can help me!

[harshnathani]

Hi @Anonymous ,

You can try a measure too.

Measur =
CALCULATE (
    SUM ( 'Table (2)'[Weight] ),
    FILTER (
        ALL ( 'Table (2)' ),
        'Table (2)'[Date & Time]
            <= MAX ( 'Table (2)'[Date & Time] )
            && 'Table (2)'[Category]
                = MAX ( 'Table (2)'[Category] )
    )
)

Regards,

Harsh Nathani

[Anonymous]

Hi @Anonymous Try this measure.

Res =

VAR dt = SELECTEDVALUE('Table'[Date & Time])

VAR filteredTable = FILTER(ALLEXCEPT('Table', 'Table'[Category]), 'Table'[Date & Time] <= dt)

Return SUMX(filteredTable, 'Table'[Weight])

Hope this helps.

Please mark this as a solution if it serves the purpose.

Thanks

[harshnathani · Accepted solution]

Hi @Anonymous ,

Make sure that the COlumn is marked as Date- Time in Power Query

Create a Calculated Column

Column =
CALCULATE (
    SUM ( 'Table (2)'[Weight] ),
    FILTER (
        'Table (2)',
        'Table (2)'[Date & Time]
            <= EARLIER ( 'Table (2)'[Date & Time] )
            && 'Table (2)'[Category]
                = EARLIER ( 'Table (2)'[Category] )
    )
)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

yes this works, thank you very much!