exclude record from analysis if prior year activity doesn't exist with time intelligence approach

[ogend]

hi BI Gurus, 

My Table looks like this:

Period	Scenario	Department	Revenue
Dec-17	Actual	department1	500
Mar-18	Forecast	department1	550
Jun-18	Forecast	department1	600
Sep-18	Forecast	department1	500
Dec-18	Forecast	department1	530
Dec-18	Actual	department1	535
Mar-19	Forecast	department1	580
Jun-19	Forecast	department1	600
Sep-19	Forecast	department1	620
Dec-19	Forecast	department1	590
Dec-19	Actual	department1	600
Mar-20	Forecast	department1	620
Jun-20	Forecast	department1	630
Sep-20	Forecast	department1	620
Dec-20	Forecast	department1	610
Dec-20	Actual	department1	600
Dec-17	Actual	department2	575
Mar-18	Forecast	department2	633
Jun-18	Forecast	department2	690
Sep-18	Forecast	department2	575
Dec-18	Forecast	department2	610
Dec-18	Actual	department2	615
Mar-19	Forecast	department2	667
Jun-19	Forecast	department2	690
Sep-19	Forecast	department2	713
Dec-19	Forecast	department2	679
Dec-19	Actual	department2	690
Mar-20	Forecast	department2	713
Jun-20	Forecast	department2	725
Sep-20	Forecast	department2	713
Dec-20	Forecast	department2	702
Dec-20	Actual	department2	700
Dec-19	Actual	department 3	345
Mar-20	Forecast	department 3	357
Jun-20	Forecast	department 3	362
Sep-20	Forecast	department 3	357
Dec-20	Forecast	department 3	351
Dec-20	Actual	department 3	350

my goal is to get to this view  so that Current year is picked from a slicer and prior year is dinamically calculated  as curent year - 1

	PY	Forecast03	Forecast06	Forecast09	Forecast12	CY
department1	600	620	630	620	610	600
department2	690	713	725	713	702	700
department 3	345	356.5	362.25	356.5	350.75	350

I was able to get to this with setting up a date table and using the year from the date table in the slicer 

Here is what i don't know how to aproach:

per above data if current year is 2019- department 3 doesn't have any activity for prior year, the record for 2018 activity doesn't exist

2018					2019
	PY	Forecast03	Forecast06	Forecast09	Forecast12	CY
department1	535	580	600	620	590	600
department2	615	667	690	713	679	690
department 3						345

i want to exclude department 3 scenario from analyis . would you please advise? If prior year record for scenario= "Actual"  doesn't exist or is 0 or is null - do not include department in the calc, however if i selected 2020 as my current year- i would need department 3 to show since it does have activity for 2019.

Thank you!

Olga

[ogend · Accepted solution]

Hi @lbendlin , 

my CY Measure is 

 CY=CALCULATE(SUM('Table'[Revenue]),FILTER('Table','Table'[Scenario]="Actual"),FILTER('Date','Date'[CurrentYear]='Date'[CurrentYear])

can you please explain what you  mean ? Sorry, i am super new to Power BI. 

Olga

[lbendlin]

Remember that measures are computed for all elements of a visual - table cells, row and column subtotals, and the grand total. That means you have to consider up to four different computations depending on what you want to achieve.

I found that starting from the perspective of the grand total is the most effective way.

[lbendlin · Accepted solution]

your last filter is meaningless

FILTER('Date','Date'[CurrentYear]='Date'[CurrentYear])

your measure seems to be missing a closing bracket.

Your measure should look similar to this:

 CY=

var m = max('Date'[CurrentYear])

var p = CALCULATE(SUM('Table'[Revenue]), 'Table'[Scenario]="Actual", FILTER('Date','Date'[CurrentYear]=m-1) )

return if (ISBLANK(p),BLANK(),CALCULATE(SUM('Table'[Revenue]),'Table'[Scenario]="Actual"))

[ogend]

Thanks for the explanation, @lbendlin , this approach does work for dropping department 3 from the visual, but subtotal in the visual is still picking up department 3. is there any way to modify the formula so it subtotals only visible rows or creating separate mesuares and using Union to bring them in place of subtotals is my only choice? 

[lbendlin]

All you need to do is BLANK() out measure CY for that scenario - then department 3 will disappear from the visual

[ogend · Accepted solution]

Hi @lbendlin , 

my CY Measure is 

 CY=CALCULATE(SUM('Table'[Revenue]),FILTER('Table','Table'[Scenario]="Actual"),FILTER('Date','Date'[CurrentYear]='Date'[CurrentYear])

can you please explain what you  mean ? Sorry, i am super new to Power BI. 

Olga