cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PBI-IK
Frequent Visitor

Previous Value on the same table

Hi

i have this table :

granted.png

 

I used this formula to take the previous row

 

previous = CALCULATE(
SUM('Fact Risk Decision Flow'[grd_granted_amount_value_euro]);
TOPN(1;
FILTER(
ALLSELECTED('Fact Risk Decision Flow');
'Fact Risk Decision Flow'[period] < MAX('Fact Risk Decision Flow'[period])
);
'Fact Risk Decision Flow'[period];
DESC
)
)
 
and it works correctly :
 
previous.png
 
but if i want keep only the last row (Jan-20) i loose my calculation and "previous" is empty.
 
I try to change ALLSELECTED by ALL/ALLEXCEPT but nothings works.
 
I'm connect to a SSAS... so, i cannot create calculated columns.
 
Any Idea ?
 
Thanks
 
3 REPLIES 3
Anonymous
Not applicable

// This should be the basic measure.
[Total Value] = SUM ( 'Fact Risk Decision Flow'[grd_granted_amount_value_euro] )

// PP = previous period
// Periods should have their own dedicated dimension.
// No slicing on fact tables should ever be performed.
// All columns in fact tables must be hidden. Only
// dimensions should be exposed and used for slicing.
[PP Value] =
var __onePeriodVisible = HASONEVALUE( Periods[Period] )
var __period = selectedvalue( Periods[Period] )
var __prevPeriod = __period - 1
var __result =
CALCULATE 
    [Total Value],
    Periods[Period] = __prevPeriod,
    REMOVEFILTERS( Periods )
)
return
	if( __onePeriodVisible, __result )

Best

D

PBI-IK
Frequent Visitor

Thanks for your reply but it's not exactly the solution that i need 😉

Indeed the period could be 2 years ago and not only -1.

I'm using SSAS 2016 so, REMOVEFILTERS and SELECTEDVALUES not works but i replace it with equivalent formula.

Thank you by advance

Anonymous
Not applicable

Hi @PBI-IK.

if you want to get the solution you're after, you have to do a bit more upfront work. Please define your problem more clearly and show all the involved structures.

Best
D

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors