Forum Discussion
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...
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.
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...
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 :smileyfrustrated:
Many thanks