Hi,
i want to analyze som economical data for some companies and my data is structured like:
Company | Parameter | Period | Data |
Company A | Revenue | 2018 | 3000 |
Company A | Revenue | 2017 | 2850 |
Company A | Revenue | 2016 | 2700 |
Company A | Profit | 2018 | 300 |
Company A | Profit | 2017 | 200 |
Company A | Profit | 2016 | 100 |
Company B | Revenue | 2017 | 8000 |
Company B | Revenue | 2016 | 6000 |
Company B | Profit | 2017 | 1000 |
Company B | Profit | 2016 | 800 |
Company C | Revenue | 2018 | 5000 |
Company C | Revenue | 2017 | 6000 |
Company C | Revenue | 2016 | 5500 |
Company C | Profit | 2018 | 250 |
Company C | Profit | 2017 | 120 |
Company C | Profit | 2016 | 200 |
I have about 20-30 companies that i want to analyze, not just three. I want to see sum revenue, for the selection of comapnies, for example 2018 compared to 2017. In this case Company B doesnt have any value registred for 2018 and in such case i would like to reuse the value for 2017.
A Pivot would look like this:
| Revenue | Revenue | Revenue | |
| Last | 2018 | 2017 | |
Company A | 3000 | 3000 | 2850 |
Company B | 8000 | 0 | 6000 |
Comapny C | 5000 | 5000 | 6000 |
Sum of total | 16000 | 8000 | 14850 |
I tried to make a measure "Last":
If (
(Sum({$<Parameter={"Revenue"},Period={"2018"}>}Data) > 0 )
, (Sum({$<Parameter={"Revenue"},Period={"2018"}>}Data))
, (Sum({$<Parameter={"Revenue"},Period={"2017"}>}Data))
)
When i use the above on a Pivot table everything looks fine except the summary wich is wrong in column "Last" (it show the same value as in colum "2018" (8000).
If i make a KPI it only gets right when i select companies who all have values for "2018" or select all who doesnt have values for "2018".
I understand that my problem depends om my first If-line but i dont know how to solve it so every help would be valuable!