Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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)
Proud to be a Super User!
@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
Proud to be a Super User!
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())
@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)
Proud to be a Super User!
@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))
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