Forum Discussion
Disaggregate accumulated
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
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 )
4 Replies
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 )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
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 NathaniDid I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
