Forum Discussion

Onaiggac's avatar
Onaiggac
Advocate II
9 years ago

Total column for SAMEPERIODLASTYEAR is wrong

Hi,

I have two Calculated column

 

AnoPre = CALCULATE(SUM(vw_VariacaoTotalResiduosXCategoria[Peso(t)]); SAMEPERIODLASTYEAR(DatasParaBI[Date]))

And

 

 

 

Δ = IFERROR((SUM(vw_VariacaoTotalResiduosXCategoria[Peso(t)]) - [AnoPre])/SUM(vw_VariacaoTotalResiduosXCategoria[Peso(t)]);BLANK())

 

I want to calculated the variation between same periods. All working fine but the column with total show me a wrong percent like this:

 

Its considering all months from 2016 and not only the period from Jan to Jun.

When I fix the AnoPre column to

AnoPre = CALCULATE(SUM(vw_VariacaoTotalResiduosXCategoria[Peso(t)]);FILTER(ALL(vw_VariacaoTotalResiduosXCategoria[Dia]); MONTH(vw_VariacaoTotalResiduosXCategoria[Dia]) <= MONTH(MAX(vw_VariacaoTotalResiduosXCategoria[Dia]))); SAMEPERIODLASTYEAR(DatasParaBI[Date]))

Its return the right value that is 0,039% but its get too slow.

Any clue to fix it?

 

Tks.

Need Help

2 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    9 years ago
    Onaiggac wrote:

    Hi,

    I have two Calculated column

     

    AnoPre = CALCULATE(SUM(vw_VariacaoTotalResiduosXCategoria[Peso(t)]); SAMEPERIODLASTYEAR(DatasParaBI[Date]))

    And

     

     

     

    Δ = IFERROR((SUM(vw_VariacaoTotalResiduosXCategoria[Peso(t)]) - [AnoPre])/SUM(vw_VariacaoTotalResiduosXCategoria[Peso(t)]);BLANK())

     

    I want to calculated the variation between same periods. All working fine but the column with total show me a wrong percent like this:

     

    Its considering all months from 2016 and not only the period from Jan to Jun.

    When I fix the AnoPre column to

    AnoPre = CALCULATE(SUM(vw_VariacaoTotalResiduosXCategoria[Peso(t)]);FILTER(ALL(vw_VariacaoTotalResiduosXCategoria[Dia]); MONTH(vw_VariacaoTotalResiduosXCategoria[Dia]) <= MONTH(MAX(vw_VariacaoTotalResiduosXCategoria[Dia]))); SAMEPERIODLASTYEAR(DatasParaBI[Date]))

    Its return the right value that is 0,039% but its get too slow.

    Any clue to fix it?

     

    Tks.

    Onaiggac

    Based on my test, the 3rd measure shall work, what do you mean "it gets too slow"? Do you experience any performance issue?

    Instead of applying the filter in the measure, could you try to use a filtered calculated table? Then apply the 2nd measure on this table?

    filteredTbl= FILTER(
vw_VariacaoTotalResiduosXCategoria,
MONTH ( vw_VariacaoTotalResiduosXCategoria[Dia] )
            <= MONTH ( MAX ( vw_VariacaoTotalResiduosXCategoria[Dia] ) )
)
    • Onaiggac's avatar
      Onaiggac
      Advocate II
      9 years ago

      Eric_Zhang tks for the answer.

      Yes, I do experience performance issue. Like more than a minute to finish the calculation versus 15s.

       

      How can I create a calculated table? I am using the DirectQuery and can only create the new measure.

       

      So, I created the

       

      filteredTbl = FILTER(ALL(vw_VariacaoTotalResiduosXCategoria[DT_OF_BI]); MONTH(vw_VariacaoTotalResiduosXCategoria[DT_OF_BI]) <= MONTH(MAX(vw_VariacaoTotalResiduosXCategoria[DT_OF_BI])))

       

       

      And tryied to use like this

       

      AnoPre = CALCULATE(SUM(vw_VariacaoTotalResiduosXCategoria[Peso(t)]); [filteredTbl]; SAMEPERIODLASTYEAR(DatasParaBI[Date]))

       

      But its returning an error like: A 'Calculate' was used in a True/False expression like a filtered calculated table. Its not allowed.