In DAX can I filter by the previous item in a list of Strings?

[cr731]

I have a table with a column representing a period number (formatted as YYYYMM) followed by a date (formatted as MM-DD-YY), which represent the period the data relates to and the date stamp of when that record was created.

Something like,

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Period[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]201701 (01-15-17)[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]201701 (01-20-17)[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]201702 (02-05-17)[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]


I want to create two calculated measures, one as a simple SUM of the Value column, and another that is the sum of the previous item in the Period column (based on an ascending sort of the list).

So based on the above example it would be like this,

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Period[/TD]
[TD]Sum of Value[/TD]
[TD]Sum of Previous Value[/TD]
[/TR]
[TR]
[TD]201701 (01-15-17)[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]201701 (01-20-17)[/TD]
[TD]15[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]201702 (02-05-17)[/TD]
[TD]20[/TD]
[TD]15[/TD]
[/TR]
</tbody>[/TABLE]

Can this be done or would I have to create an index on my Period column so the values are numbers?

Thanks

 

[Matt Allington]

I would create a calendar table to join to your period column. Add a unique iD index in your calendar table, then write a custom time intelligence formula to return the previous value.

Take a read here. DAX Time Intelligence Explained - Excelerator BI

 

[krrishkrsna]

convert your data to Table format. menu PowerQuery > click From Table/Range > click menu Add Column in Query Editor > click Index Column drop down menu > select From 0


above will add column Index to your table in Query Editor (syntax in Advanced Editor #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1)


remove the space from #"Added Index" in Advanced Editor
place code #"PrvVal" = Table.AddColumn(#"AddedIndex","PrvValue",each try AddedIndex[Value]{[Index]-1} otherwise 0)


let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Sales", Int64.Type}}),
#"AddedIndex" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
#"PrvVal" = Table.AddColumn(#"AddedIndex","PrvValue",each try AddedIndex[Sales]{[Index]-1} otherwise 0)
in
#"PrvVal"


i think above solves your problem