Sum of previous Periods Grouped

[Anonymous]

Hi Experts

I have a column which is text and groups my values based on which month the data was uploaded into the power bi table.

i want a measure which gives me the total for the previous period (see table below) 

if i select from the slcier P06 then return back the total for P05 which is 5 and so on

Uploaded	Month	Values	Total
Charging_P04	Apr-21	1	1
Charging_P05	Apr-21	2
Charging_P05	May-21	3	5
Charging_P06	Apr-21	4
Charging_P06	May-21	5
Charging_P06	Jun-21	6	15

[ryan_mayu]

@Anonymous 

maybe you can try create a column

Period = 
VAR p=find("P",'Table'[Uploaded],1)
return mid('Table'[Uploaded],p+1,100)*1

then create a measure

Measure = 
VAR p=max('Table'[Period])
return sumx(FILTER(all('Table'),'Table'[Period]=p-1),'Table'[Values])

pls see the attachment below

[Anonymous]

Hi Ryan - thanks for the excellent feedback - how would you amend if the uploaded column had blank empty cells.

the following is also not working
Period =
VAR p=find("P",'Table'[Uploaded],1)
return if(not(isblank('Table'[Uploaded])) , MID('Table'[Uploaded],p+1,100)*1 , blank())

[ryan_mayu · Accepted solution]

@Anonymous 

then what's the expected output? The blank cells do not belong to any period?

pls try this

Period = 
VAR p=find("P",'Table'[Uploaded],1,0)
return if(p=0,blank(),mid('Table'[Uploaded],p+1,100)*1)

[amitchandak]

@Anonymous , Split Uploaded based on _ in power query into columns  https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

Then create a new table with distinct period

Create rank column on period

Period Rank = RANKX(all(Period),Period[period],,ASC,Dense)

Then you can measure like these examples
This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])))
Last Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])-1))

[Anonymous]

Hi Amit - the Last Period = CALCULATE('Table'[Qty], FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])-1)) - is not workig getting error message the MAX function only expects column reference as an agrument