Measures Issue

[Anonymous]

Hi team,

I want a help in the measure formula..

If I am selecting Currency Euro, then prev spend in sterling should return the value prevspend/1.3, but it is not getting the exact value when spend is null..

Formula of prev spend is 

PREVSPEND = if(areaSummary[Datediff]<=365,(CALCULATE([TOTALSPEND2],SAMEPERIODLASTYEAR(Dates[Date]))),blank())

Previous Spend in Sterling = IF(areaSummary[Currency]=2,[PREVSPEND]/1.3,[PREVSPEND])

Currency here is calculated from a currencyID column as Currency= MAX(areasummary[currencyid])

Can anyone help me ?

[amitchandak]

@Anonymous ,

Better you should have a column liek
Spend con = if(areaSummary[Currency]=2, [Spend]/1.3 ,[Spend] ) //assuming spend is the column in table

in the measure it should work like
Previous Spend in Sterling = IF(max(areaSummary[Currency])=2,[PREVSPEND]/1.3,[PREVSPEND]) //but it will depedent on row context

[Anonymous]

@amitchandak 

Spend is measure here and that is working fine...Issue is in the prev spend in sterling measure..

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

The main issue is it is working on else statement always when the spend value in not there for prev spend..

[Anonymous]

@amitchandak 

https://app.powerbi.com/view?r=eyJrIjoiNDg1ZTdhZDMtYjI4Zi00MWM3LTg4NTQtOWJkYjdmZWQ0NjlkIiwidCI6ImU4N...  

Please check from here if this will help you..

[Anonymous]

@amitchandak  any suggestions ?

[amitchandak]

@Anonymous , The visual is not loading

[Anonymous]

@amitchandak  not able to attach a file here as there is no option...How can I showcase you?