Measure on a chart doesn't take into account values from other years

[PwrBI01]

Hi guys,

I want to do the following chart:

When I create it, the result for the measure 'Not year-to-date sales or forecast' for 2019 shows -547.137 instead of 443.145, as you can see in the photo:

The problem is in the measure 'Forecast', that is integrated in the measure 'Not year-to-date sales or forecast'.

This is due to:

Forecast = (Sales 12 last months / Sales 12 previous months * Total sales previous year) - Year-to-date sales

So, if the selected period is June 2019 the variables are the following ones:

1. Sales 12 last months = Sales from July 2018 to June 2019

2. Sales 12 previous months = Sales from July 2017 to June 2018

3. Total sales previous year = Sales from January 2018 to December 2018

4. Year-to-date sales = Sales from January 2019 to June 2019

The complete measure is:

Forecast =

Var Sales12LastMonths = CALCULATE([Sales];DATESINPERIOD('Calendar table'[Date];MAX('Calendar table'[Date]);-12;MONTH))

Var Sales12PreviousMonths = CALCULATE([Sales];DATESINPERIOD('Calendar table'[Date];ENDOFMONTH(dateadd('Calendar table'[Date];-12;MONTH));-12;MONTH))

Var TotalSalesPreviousYear = CALCULATE([Sales];DATESYTD(ENDOFYEAR(DATEADD('Calendar table'[Date];-1;YEAR));"12/31"))

Var YearToDateSales = CALCULATE([Sales];DATESYTD('Calendar table'[Date];"12/31"))

Var Forecast = (Sales12LastMonths/Sales12PreviousMonths*TotalSalesPreviousYear)-YearToDateSales

Return

Forecast

When I introduce the measure on a chart and put a slicer for year 2019, the variables 'Sales 12 last months', 'Sales 12 previous months' and 'Total sales previous year' show a wrong result, because it doesn't take into account the sales from 2017 and 2018, so for example the result for 'Sales 12 previous months' is 0, as you can see in the left part of the image above.

How can I solve this?

The file is in the following link: https://drive.google.com/file/d/1E_1-DivcRO_OKWmiu_0vokB1DDA6xnhA/view?usp=sharing 

Thanks in advance.

Regards.

[AlB · Accepted solution]

Hi @PwrBI01 

Thanks for the detailed explanation and the attached file. If all questions were stated like this, it'd save us lots of time...

I do get a bit lost looking at the file and code, why you use EXPEDIENTE[Year] in the x-axis of the chart, etc.. but try this small modification to your measure:

Not year-to-date sales or forecast =
VAR SelectedValue = SELECTEDVALUE ( 'Calendar table'[Year] )
RETURN
    IF (
        YEAR ( MAX ( EXPEDIENTE[Date] ) ) = SelectedValue,
        CALCULATE ([Forecast], ALL ( EXPEDIENTE[Year] ),'Calendar table'[Year] = SelectedValue),
        [NotYTDSalesPreviousYear]
    )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[PwrBI01]

Hi @AlB,

I wanted to create a chart with two years, the selected year and the previous one. After tried several ways to get it in the chart, the only way I found to do it is with two calendar tables (Expediente and Calendar table).

The reason for that weird measure with both calendar tables is because if I change the filter 'Expediente'[Year] in the chart and I put the filter 'Calendar table'[Year], the result is the correct one, but I can't see both years, as you can see on the photo.

Thank you to your answer I have found how to solve it after more than 1 month trying to get it. I really appreciate your solution.

Regards.