Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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 |
Solved! Go to 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
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
@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:
User | Count |
---|---|
57 | |
21 | |
19 | |
18 | |
16 |
User | Count |
---|---|
85 | |
80 | |
52 | |
37 | |
22 |