IF result value exist, ignore prognosis value and only show result

[Anonymous]

I'm trying to create a matrix where a projects income and cost can have a prognosis income/cost, and the result income/cost. 

So something like this:

ProjectID	Year	Type	C/I	Value
1	2018	Prognosis	Income	50 000
1	2018	Result	Income	70 000
1	2018	Prognosis	Cost	30 000
1	2018	Result	Cost	35 000
x	etc etc
2	2022	Prognosis	Income	75 000
2	2022	Prognosis	Cost	75 000

So what I want in my matrix/measure is when there is a result, I want to display only that value, not the prognosis. And when there is only a prognosis, only show the prognosis. 

So for example for year 2018, i would only want to see the results of income and cost. 

And for 2022 only the prognosis for income and cost. 

Any tips for how to achieve this? 

Thanks

[Ashish_Mathur · Accepted solution]

Hi,

I believe this is the result you want.  You may download my PBI file from here.

Hope this helps.

[Anonymous]

Thank you!

[Ashish_Mathur]

You are welcome.

[Anonymous]

@Ashish_Mathur  

Another question, for some projects there is only a prognosis in earlier year, this makes them get considered in the new column. Is there a way to get them ignored instead? What do I need to adjust in the column? 

Example:

ProjectID	Year	Type	C/I	Value	Consider/Ignore
160	2009	Prognosis	Cost	12 000	Ignore
160	2009	Result	Cost	12 008	Consider
160	2009	Prognosis	Income	100 000	Consider

In example above I would like the Income prognosis to be ignored. 

Thanks!

[Ashish_Mathur]

Hi,

This logic seems inconsistent with tha of the previous years.  I cannot figure out a common logic which will apply to all rows.

[Anonymous]

I see, thank you. 

I'm thinking something like everything earlier than todays year should be ignored, unless type = Result but didn't manage to get the formula working correctly. Then combine it with other parts for prognosis 

[Anonymous]

Hi @Anonymous ,

Based on my test, you could create a flag measure and then apply it to filter pane:

Flag = 
var _t=SUMMARIZE(FILTER(ALL('Table'),[ProjectID]=MAX('Table'[ProjectID]) && [Year]=MAX('Table'[Year])),'Table'[Type])
return IF( COUNTX(_t,[Type]) =1 && MAX('Table'[Type])= "Prognosis" || ("Result" in _t && MAX('Table'[Type]) ="Result") ,1)

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Anonymous Thanks!

Unfortunately for some reason it didn't appear to work for me. Maybe because the model got updated shortly after this post and I now have another "type",  budget as well. So Budget, Prognosis and Result. 

Anyway, with this measure in place, I can in some places still see both prognosis and results for previous years. 

[amitchandak]

@Anonymous , Based on what I got so far, You need to create a new column  like

new column =

var _cnt1= countx(filter(Table, [Year] =earlier([year]) ) , [Type]) +0

var _cnt2= countx(filter(Table, [Year] =earlier([year]) && [Type] ="Prognosis" ) , [Type]) +0

return

if(_cnt1=_cnt2 , "Prognosis", [C/I])

[Anonymous]

@amitchandak 

Thanks for the reply!

Sadly this didn't seem to work, unless I did something wrong. 

I still see the same results, meaning I can still see that some years show both Prognosis and Results

Edit: I tried making a column that checks if the year is before 2021, and if so sets value to "Result", else "Prognosis": 

IF(financial[year]<2021-01-01, "Result", "Prognosis")

But something with this isn't quite right, since all values in the column end up being "Prognosis" 

I then wanted to use this as a way to get the values into the matrix, but I get an error saying

"A function 'CONTAINSROW' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

if(financial[NewColumn]= "Result", CALCULATE(SUM(financial[value]), financial[value] IN {financial[type] = "Result"}), CALCULATE(SUM(financial[value]),financial[value] IN {financial[type] = "Prognosis"}))