Problem with IF(VALUES(Calendar[MONTHCODE])) What is happening?

[lcerrapa]

Hello all.

I wasted several hours in this calculations with no results for me. The final (and wrong, I think) results I got are the following:

I have two main measures, one for real data and another for the forecast data and I need tho show the real measure if the month is less or equal than the real month and the forecast measure otherwise. I have no data for 2016.

This is the measure definitions (I don't write all definitions for clarity reason but if it is neccesary I can write all):

Monthcode (it appears to work):

LCP_Month:=IF( HASONEVALUE( Calendario[MONTHCODE] );VALUES(Calendario[MONTHCODE]);"SEVERAL MONTHS")

Real Month (it apperas to work):

RealMonth:=MIN(V_Real[MES])

Real Data:

LCP_RealVariation:= -1*(
  CALCULATE(
    [varExMP];
    Filter(ALL('Raw Material'); OR('Raw Material'[LVL3CODE]="11";'Raw Material'[LVL3CODE]="21"));
    FILTER(Calendario; Calendario[MONTHCODE]<=[mesReal])
  ) )

varExMP:=SUM(Existences[Existences Variation])

LCP_ForecastVariation:=[LCP_FixedValue] * [Month Production] --> Month Production is only a sum of a column from other table

From here, the things I can't understand:

LCP_VarExMp_Test1:=
  IF( HASONEVALUE( Calendario[MONTHCODE] );
      IF( DISTINCT(Calendario[MONTHCODE]) <= [RealMonth];
          [LCP_RealVariation];
          [LCP_ForecastVariation]
      );
      "NO MONTH SELECTED"
  )

As you can see in the next measures, the issue appears when I use 'DISTINCT(Calendario[MONTHCODE])'

LCP_VarExMp_Test2:=
  IF( HASONEVALUE( Calendario[MONTHCODE] );
      IF( 3 > [RealMonth];    --> No sense, only for demostration purposes
          VALUES(Calendario[MONTHCODE]); [LCP_ForecastVariation]
      );
  "NO MONTH SELECTED"

)

LCP_VarExMp_Test3:=
    IF( HASONEVALUE( Calendario[MONTHCODE] );
        IF( DISTINCT(Calendario[MONTHCODE]) > [RealMonth];
            [LCP_RealVariation];
            [LCP_ForecastVariation]
        );
      "NO MONTH SELECTED"
    )

I appreciate every suggestions, I am really lost and frustrated.

Thank you.

[mattbrice]

Is your actual and forecast data in the same table?  is the table V_Real connected to other fact tables? And is V_Real a best practice calendar table?  Any other Calendar columns in visual/page/report filter areas?

What you are trying to do is something I have done albeit in Excel/Power Pivot.  But Dax should be the same.

[lcerrapa]

Hello @mattbrice

The forecast uses a formula with data from two tables, one is the same table than the real data and other different table. The table V_Real is not connected with any table in the model because it only recover a month number to determine wich month is currently the real month I have a Calendar table in the model, connected with all the fact tables wich have date fields, and it is the only 'valid' date table in the model. Always the year, months, quarters are taken from this Calendar table.

My issue occurs only in Power BI Desktop, in Excel it works fine.

I have more information as you can see in the images bellow. Always I take the year from the Calendar table. If I show this same year in the matrix, the results are wrong:

If I remove the year from the matrix, the result is right:

Both matrix and slicer takes the year from the Calendar table.

Thank you.

[mattbrice · Accepted solution]

While it is hard to say without being very familiar with your info, it looks to me  like the issue is in the LCP_ForecastVariation measure.   I would look at how the [LCP_FixedValue] and [Month Production] measures are calculated - does adding the Calendario[Ano] column to their filter context alter their values?  

And also from what i can see in the LCP_RealVariation measure, the line :  FILTER(Calendario; Calendario[MONTHCODE]<=[mesReal] is going to be in contrast to the LCP_VarExMp_Test3 second IF statement causing a blank result.

My first ideas...

[lcerrapa]

Hello @mattbrice

I deleted all the measures and I wrote all it again. There was an error on a measure that affects all dependent measures (and I don't found it). Now it's working.

Many thanks for your time. 

[lcerrapa]

Hello @mattbrice

 I know it's hard to understand this model, thanks for your effort.

In fact, [LCP_FixedValue] changes its value when I add or remove the Calendario[Ano] column. I will try to investigate the reason it changes the value adding the Calendario[Ano], because it uses measures relatively simple, with sum commands.

The LCP_VarExMp_Test3 measure is only for demostration, to see the big variation wich occurs adding or removing the Calendar[Ano] to the matrix.

I hope tomorrow tell you this is working, I don't know if it will be possible 

Many thanks