Different result in a chart and in a target for the same measure

[PwrBI01]

Hi guys,

I have a measure on a chart that should do the following:

1. Show the measure NotYTDSales for year 2018

2. Show the measure Forecast for year 2019

The problem is that if I put the measure Forecast on a target it shows the correct result, but if I put it on the chart or put the measure 'Forecast 2019 = CALCULATE([Forecast];EXPEDIENTE[Año]=2019)' it shows a different result, as you can see on the photo.

The measure Forecast is the next one:

Forecast =

Var CurrentDate = If(COUNTROWS(DISTINCT(ALLSELECTED('T_M_Tabla de fechas'[Fin de mes])))=1;SELECTEDVALUE('T_M_Tabla de fechas'[Fin de mes]);max('T_M_Tabla de fechas'[Fin de mes]))

Var OneYearBeforeCurrentDate = DATE(YEAR(CurrentDate)-1;MONTH(CurrentDate);DAY(CurrentDate))

Var TwoYearsBeforeCurrentDate = DATE(YEAR(CurrentDate)-2;MONTH(CurrentDate);DAY(CurrentDate))

Var StartCurrentDate = DATE(YEAR(CurrentDate);1;1)

Var StartYearBeforeCurrentDate = DATE(YEAR(CurrentDate)-1;1;1)

Var EndYearBeforeCurrentDate = DATE(YEAR(CurrentDate)-1;12;31)

Var TAMSales = CALCULATE(SUM(EXPEDIENTE[ARANCELBRUTO]);filter(EXPEDIENTE;EXPEDIENTE[Fin de mes]>OneYearBeforeCurrentDate && EXPEDIENTE[Fin de mes]<=CurrentDate))

Var TAMPreviousSales = CALCULATE(SUM(EXPEDIENTE[ARANCELBRUTO]);FILTER(EXPEDIENTE;EXPEDIENTE[Fin de mes]>TwoYearsBeforeCurrentDate && EXPEDIENTE[Fin de mes]<=OneYearBeforeCurrentDate))

Var TotalPreviousSales = CALCULATE(SUM(EXPEDIENTE[ARANCELBRUTO]);FILTER(EXPEDIENTE;EXPEDIENTE[Fin de mes]>=StartYearBeforeCurrentDate && EXPEDIENTE[Fin de mes]<=EndYearBeforeCurrentDate))

Var SalesYTD = CALCULATE(SUM(EXPEDIENTE[ARANCELBRUTO]);FILTER(EXPEDIENTE;EXPEDIENTE[Fin de mes]>=StartCurrentDate && EXPEDIENTE[Fin de mes]<=CurrentDate))

Var Forecast = Calculate((TAMSales/TAMPreviousSales*TotalPreviousSales)-SalesYTD)

Return

Forecast

The link is in the first answer.

Thanks in advance.

[PwrBI01]

Hi @Anonymous, thank you for your answer.

On the one hand, the result I want to obtain is 443145 (this is correct result if there is no filter for year 2019).

According to the measure forecast, the problem is that if I put a filter for year 2019 the result for TAMPreviousSales = 0, because it takes into consideration the period July 2017 to June 2018, so it doesn't take into consideration the results because they are not in the year 2019, but I don't know how to solve this.

As you can see, if the result for TAMPreviosSales = 0, then Forecast = -SalesYTD, that is the result that is showing. I send you a sum up of the measures I am talking about.

Var TAMPreviousSales = CALCULATE(SUM(EXPEDIENTE[ARANCELBRUTO]);FILTER(EXPEDIENTE;EXPEDIENTE[Fin de mes]>TwoYearsBeforeCurrentDate && EXPEDIENTE[Fin de mes]<=OneYearBeforeCurrentDate))

Var Forecast = Calculate((TAMSales/TAMPreviousSales*TotalPreviousSales)-SalesYTD)

On the other hand, there are 2 calendar tables with no relationship between the tables as you said. The reason I did it is to show the selected year and the one before in the chart, because if I don't do this it just shows the selected year.

Thanks in advance.

Jorge.

[Anonymous]

@PwrBI01 
I doubt 443145 is the value that correctly filtered to 2019, but the forecast measure is overly long I could not follow it for test.

The problem is the 2 tables are unrelated, and the used variables in the forecast measure are filtered by the EXPEDIENTE table in the expressions, but the year slicer in the pbix is from the T_M_Tabla de fechas table and it is invalid to filter forecast measure. If you create a year slicer using EXPEDIENTE[Año] and select 2019, the forecast card value will be -547137 that is filtered to year 2019.

Regards
Paul

[PwrBI01]

The link for the .pbix is https://drive.google.com/file/d/1k2mdxQb_WyHnWGEDfURaT_frixxJYZYC/view?usp=sharing