filter propagation

[jcamilo1985]

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

[Anonymous · Accepted solution]

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])

Best regards

Rena

[jcamilo1985]

@yingyinr simply brilliant, thank you very much for your help, congratulations