cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lcerrapa Regular Visitor
Regular Visitor

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

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:

 

Matriz.JPG

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
mattbrice Senior Member
Senior Member

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

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...

View solution in original post

5 REPLIES 5
mattbrice Senior Member
Senior Member

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

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 Regular Visitor
Regular Visitor

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

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:

MatrizYear.JPG

 

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

MatrizNOYear.JPG

 

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

 

Thank you.

Highlighted
mattbrice Senior Member
Senior Member

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

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...

View solution in original post

lcerrapa Regular Visitor
Regular Visitor

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

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 Smiley Frustrated

 

Many thanks

lcerrapa Regular Visitor
Regular Visitor

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

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. 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 229 members 2,390 guests
Please welcome our newest community members: