help with conditional statement

[anna-lee]

Hello,

Currently having difficultly finding a way to create a conditional measure based off the sum of the previous year. I currently have two measures in place posted below iwth the data set. I'd like to get a conditional statement to give  : If sum of previous year of 'Total Value' > 0, then 'Receivables Turnover', else 0

Total Value = CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[Vendor Code],'Table'[Year]))

Receivables Turnover =

  VAR Year = MAX('Table'[Year])

  VAR Rev = SUMX(FILTER('Table',[Description] = "revenue" && 'Table'[Vendor Code] = SELECTEDVALUE('Table'[Vendor Code])),[Value])

  VAR AR = SUMX(FILTER('Table',[Description] = "accountsreceivable" && 'Table'[Vendor Code] = SELECTEDVALUE('Table'[Vendor Code])),[Value])

  VAR ARPY = SUMX(FILTER(ALL('Table'),[Description] = "accountsreceivable" && [Year] = Year - 1 && 'Table'[Vendor Code] = SELECTEDVALUE('Table'[Vendor Code])),[Value])

  VAR RT = DIVIDE(Rev, (AR + ARPY) / 2)

RETURN

    RT

Vendor Code	Description	Concat	Year	Value	Result
MA33C4	accountsreceivable	MA33C4accountsreceivable	2021	1774	14194
MA33C4	accountsreceivable	MA33C4accountsreceivable	2020	1501	13904
MA33C4	accountsreceivable	MA33C4accountsreceivable	2019	1103	10484
MA33C4	accountsreceivable	MA33C4accountsreceivable	2018	4615	20214
MA33C4	accountsreceivable	MA33C4accountsreceivable	2017	6852	20017
MA33C4	accountsreceivable	MA33C4accountsreceivable	2016	1827	10004
MA33C4	accountspayable	MA33C4accountspayable	2021	1094	14194
MA33C4	accountspayable	MA33C4accountspayable	2020	1697	13904
MA33C4	accountspayable	MA33C4accountspayable	2019	2144	10484
MA33C4	accountspayable	MA33C4accountspayable	2018	3498	20214
MA33C4	accountspayable	MA33C4accountspayable	2017	3624	20017
MA33C4	accountspayable	MA33C4accountspayable	2016	1109	10004
MA33C4	revenue	MA33C4revenue	2021	1480	14194
MA33C4	revenue	MA33C4revenue	2020	1502	13904
MA33C4	revenue	MA33C4revenue	2019	1002	10484
MA33C4	revenue	MA33C4revenue	2018	7624	20214
MA33C4	revenue	MA33C4revenue	2017	5489	20017
MA33C4	revenue	MA33C4revenue	2016	4850	10004
MA33C4	costofgoods	MA33C4costofgoods	2021	9846	14194
MA33C4	costofgoods	MA33C4costofgoods	2020	9204	13904
MA33C4	costofgoods	MA33C4costofgoods	2019	6235	10484
MA33C4	costofgoods	MA33C4costofgoods	2018	4477	20214
MA33C4	costofgoods	MA33C4costofgoods	2017	4052	20017
MA33C4	costofgoods	MA33C4costofgoods	2016	2218	10004
RQ020G	accountsreceivable	RQ020Gaccountsreceivable	2021	0	0
RQ020G	accountsreceivable	RQ020Gaccountsreceivable	2020	1009	11802
RQ020G	accountsreceivable	RQ020Gaccountsreceivable	2019	1903	12597
RQ020G	accountsreceivable	RQ020Gaccountsreceivable	2018	1566	11546
RQ020G	accountsreceivable	RQ020Gaccountsreceivable	2017	1083	13891
RQ020G	accountsreceivable	RQ020Gaccountsreceivable	2016	0	0
RQ020G	accountspayable	RQ020Gaccountspayable	2021	0	0
RQ020G	accountspayable	RQ020Gaccountspayable	2020	4307	11802
RQ020G	accountspayable	RQ020Gaccountspayable	2019	2743	12597
RQ020G	accountspayable	RQ020Gaccountspayable	2018	2272	11546
RQ020G	accountspayable	RQ020Gaccountspayable	2017	2397	13891
RQ020G	accountspayable	RQ020Gaccountspayable	2016	0	0
RQ020G	revenue	RQ020Grevenue	2021	0	0
RQ020G	revenue	RQ020Grevenue	2020	3582	11802
RQ020G	revenue	RQ020Grevenue	2019	4239	12597
RQ020G	revenue	RQ020Grevenue	2018	4000	11546
RQ020G	revenue	RQ020Grevenue	2017	5325	13891
RQ020G	revenue	RQ020Grevenue	2016	0	0
RQ020G	costofgoods	RQ020Gcostofgoods	2021	0	0
RQ020G	costofgoods	RQ020Gcostofgoods	2020	2904	11802
RQ020G	costofgoods	RQ020Gcostofgoods	2019	3712	12597
RQ020G	costofgoods	RQ020Gcostofgoods	2018	3708	11546
RQ020G	costofgoods	RQ020Gcostofgoods	2017	5086	13891
RQ020G	costofgoods	RQ020Gcostofgoods	2016	0	0

[Tanushree_Kapse · Accepted solution]

Hi @anna-lee ,

Create following measures:
PY Total Value= CALCULATE(Table[Total Value], PREVIOUSYEAR(Table[YEAR]))
------please make sure the YEAR column is in date format.

Measure= 
if(PY Total Value >0, Receivables Turnover, 0)

Mark this as a solution, if I answered your question. Kudos are always appreciated.

Thanks

[anna-lee]

@Tanushree_Kapse 
Thanks. It works in theory, but when I change my Year column to a date, it changes the output of my original 'Receivables Turnover' measure to incorrect numbers. 

I did a work around where I could keep my Year column as an integer by adding a measure:

Value PY =

    VAR Year = MAX('Table'[Year])

    VAR PY = Year - 1

RETURN

    CALCULATE([Total Value],'Table'[Year] = PY)