Disaggregate accumulated

[DanCasSan]

Hello community.

How could I get the highlighted field? What happens is that I have the accumulated information per day, but I need to know the value of the day. I have attached an example table.

Thank you!

Regards,

Daniel Castillo

[harshnathani]

Hi @DanCasSan ,

You can create a Calculated Column

Column =

var PreviousDate =

CALCULATE (

    MAX ( 'Table'[Date] ),

    FILTER (

        ALLEXCEPT (

            'Table',

            'Table'[CountryNam]

        ),

        'Table'[Date]

            < EARLIER ( 'Table'[Date] )

    )

)

var _rankOrder = RANKX(FILTER('Table', 'Table'[CountryNam] = EARLIER('Table'[CountryNam])),'Table'[Date],,ASC)

var PreviousValAcum =

CALCULATE (

    MAX('Table'[ValueAcum]),

    FILTER (

        ALLEXCEPT (

            'Table',

            'Table'[CountryNam]

        ),

        'Table'[Date]

            < EARLIER('Table'[Date])  && 'Table'[Rank Date] = _rankOrder - 1

    )

)

return

'Table'[ValueAcum] - PreviousValAcum

Regards,
Harsh Nathani

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

[az38 · Accepted solution]

Hi @DanCasSan 

try a column

Column = 
var _prevDate = CALCULATE(MAX(Table[Date]), FILTER(ALL(Table), Table[CountryName] = EARLIER(Table[CountryName]) && Table[Date] < EARLIER(Table[Date]) ) )
var _thisVal = EARLIER(Table[ValueAccuum])
RETURN
_thisVal - CALCULATE(MAX(Table[ValueAccuum]), ALLEXCEPT(Table, Table[CountryName]), Table[Date] = _prevDate )

[DanCasSan]

@az38 thank you!
This worked perfectly for me

Regards,

Daniel Castillo

[harshnathani]

Hi @DanCasSan ,

Check out this blog.

You can get the previous date and value in the same row and then subtract the value of the columns

http://harshnathani.blogspot.com/2020/06/find-previous-details-of-customer-in.html

Column =

var PreviousDate =

CALCULATE (

    MAX ( 'Table'[Date] ),

    FILTER (

        ALLEXCEPT (

            'Table',

            'Table'[CountryNam]

        ),

        'Table'[Date]

            < EARLIER ( 'Table'[Date] )

    )

)

var _rankOrder = RANKX(FILTER('Table', 'Table'[CountryNam] = EARLIER('Table'[CountryNam])),'Table'[Date],,ASC)

var PreviousValAcum =

CALCULATE (

    MAX('Table'[ValueAcum]),

    FILTER (

        ALLEXCEPT (

            'Table',

            'Table'[CountryNam]

        ),

        'Table'[Date]

            < EARLIER('Table'[Date])  && 'Table'[Rank Date] = _rankOrder - 1

    )

)

return

'Table'[ValueAcum] - PreviousValAcum

Regards,

Harsh Nathani