Forum Discussion

jcamilo1985's avatar
jcamilo1985
Helper III
5 years ago
Solved

filter propagation

Good Morning
I think I have done something wrong in my data model, model OneDrive, and I do not know how to solve it, for this reason I go back to you that you can always guide me in the best way.

 

the situation is that, on the one hand, the time measurements are not working as expected, for example with the previousyear or previousmonth functions the calculation is not performed and the truth is I do not know why this happens.

    

Another aspect is that I need a table that shows the fulfillment of the goal that is in the table "regional goals" discriminated by date, but it is not filtered in the correct way despite the fact that they are with both a filter type between regions and goals.

Can someone please tell me why this behavior is occurring

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hello @jcamilo1985 ,

    I have updated your file, you can get it from this link. Check if the values are correct. Updated information as follows:

    1. Update the measurement formula [ sales lastyear] and [ Last monthsales]

    ventas año pasado = CALCULATE(ventas[ventas_pbs],SAMEPERIODLASTYEAR('calendario'[keyDate]))​
    Ventas ultimo mes = CALCULATE(ventas[ventas_pbs],DATEADD('calendario'[keyDate],-1,MONTH))

    2. Create 3 measures and update the measurement formula [goal_sales_regional]

    1)Get the sum of commercial pbs2 in table metas_director

    00_Meta Comercial Pbs2 = 
var _curdate=MAX('calendario'[keyDate])
return CALCULATE(SUM('metas_director'[Meta Comercial Pbs2]),FILTER('metas_director','metas_director'[fecha]=_curdate))​

    Measure = SUMX(VALUES('calendario'[keyDate]),[00_Meta Comercial Pbs2])​

    2) Get the sum of commercial pbs2 in table metas_regionals

    goal_sales_regional = 
var _curdate=MAX('calendario'[keyDate])
return CALCULATE(SUM(metas_regionales[Meta Comercial Pbs2]), FILTER('metas_regionales','metas_regionales'[fecha]=_curdate))​​
    Measure 2 = SUMX(VALUES('calendario'[keyDate]),[goal_sales_regional])

    filter propagation.JPG

    Best regards

    Rena

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello @jcamilo1985 ,

    I have updated your file, you can get it from this link. Check if the values are correct. Updated information as follows:

    1. Update the measurement formula [ sales lastyear] and [ Last monthsales]

    ventas año pasado = CALCULATE(ventas[ventas_pbs],SAMEPERIODLASTYEAR('calendario'[keyDate]))​
    Ventas ultimo mes = CALCULATE(ventas[ventas_pbs],DATEADD('calendario'[keyDate],-1,MONTH))

    2. Create 3 measures and update the measurement formula [goal_sales_regional]

    1)Get the sum of commercial pbs2 in table metas_director

    00_Meta Comercial Pbs2 = 
var _curdate=MAX('calendario'[keyDate])
return CALCULATE(SUM('metas_director'[Meta Comercial Pbs2]),FILTER('metas_director','metas_director'[fecha]=_curdate))​

    Measure = SUMX(VALUES('calendario'[keyDate]),[00_Meta Comercial Pbs2])​

    2) Get the sum of commercial pbs2 in table metas_regionals

    goal_sales_regional = 
var _curdate=MAX('calendario'[keyDate])
return CALCULATE(SUM(metas_regionales[Meta Comercial Pbs2]), FILTER('metas_regionales','metas_regionales'[fecha]=_curdate))​​
    Measure 2 = SUMX(VALUES('calendario'[keyDate]),[goal_sales_regional])

    filter propagation.JPG

    Best regards

    Rena